Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
plus 1
Hello Experts,
I have an xls worksheet in which users paste a date from another application. Once pasted, I want to increase the value by one day (in the same field). Is that possible? And if so, how? alex |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
plus 1
enter 1 in a cell copy it
select the cell with the date paste special add this should increase the day by one, assuming what was pasted was a date and not text that looked like a date. "alex" wrote: Hello Experts, I have an xls worksheet in which users paste a date from another application. Once pasted, I want to increase the value by one day (in the same field). Is that possible? And if so, how? alex |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
plus 1
Enter 1 in a cell.
Copy that cell. Select the date cell. Paste SpecialAddOKEsc. Re-format as date. Gord Dibben MS Excel MVP On Fri, 13 Jul 2007 09:58:28 -0700, alex wrote: Hello Experts, I have an xls worksheet in which users paste a date from another application. Once pasted, I want to increase the value by one day (in the same field). Is that possible? And if so, how? alex |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
plus 1
On Jul 13, 5:13 pm, Gord Dibben <gorddibbATshawDOTca wrote:
Enter 1 in a cell. Copy that cell. Select the date cell. Paste SpecialAddOKEsc. Re-format as date. Gord Dibben MS Excel MVP On Fri, 13 Jul 2007 09:58:28 -0700, alex wrote: Hello Experts, I have an xls worksheet in which users paste a date from another application. Once pasted, I want to increase the value by one day (in the same field). Is that possible? And if so, how? alex- Hide quoted text - - Show quoted text - Thanks (to both) for your help. Your suggestions obviously worked. Would you happen to know the VBA code to accomplish the same task, without the extra step? I'd like users to simply paste in the date and have said date automatically increase by one. alex |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
plus 1
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count 1 Then Exit Sub Application.EnableEvents = False With Target .Value = .Value + 1 .NumberFormat = "dd mm yyyy" Application.EnableEvents = True End With End Sub This is sheet event code. Right-click on the sheet tab and copt/paste the code into that sheet module. Gord On Mon, 16 Jul 2007 03:02:12 -0700, alex wrote: Thanks (to both) for your help. Your suggestions obviously worked. Would you happen to know the VBA code to accomplish the same task, without the extra step? I'd like users to simply paste in the date and have said date automatically increase by one. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
plus 1
On Jul 17, 12:15 pm, Gord Dibben <gorddibbATshawDOTca wrote:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Count 1 Then Exit Sub Application.EnableEvents = False With Target .Value = .Value + 1 .NumberFormat = "dd mm yyyy" Application.EnableEvents = True End With End Sub This is sheet event code. Right-click on the sheet tab and copt/paste the code into that sheet module. Gord On Mon, 16 Jul 2007 03:02:12 -0700, alex wrote: Thanks (to both) for your help. Your suggestions obviously worked. Would you happen to know the VBA code to accomplish the same task, without the extra step? I'd like users to simply paste in the date and have said date automatically increase by one.- Hide quoted text - - Show quoted text - Thanks for your help Gord. This code works; however, only when I type the values and not when they're pasted from another application, including Excel. alex |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
plus 1
I tested before posting and tested again just now.
Works one cell at a time as you paste a date to each cell. Are you trying to paste a range of cells? Gord On Thu, 19 Jul 2007 07:18:21 -0700, alex wrote: On Jul 17, 12:15 pm, Gord Dibben <gorddibbATshawDOTca wrote: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Count 1 Then Exit Sub Application.EnableEvents = False With Target .Value = .Value + 1 .NumberFormat = "dd mm yyyy" Application.EnableEvents = True End With End Sub This is sheet event code. Right-click on the sheet tab and copt/paste the code into that sheet module. Gord On Mon, 16 Jul 2007 03:02:12 -0700, alex wrote: Thanks (to both) for your help. Your suggestions obviously worked. Would you happen to know the VBA code to accomplish the same task, without the extra step? I'd like users to simply paste in the date and have said date automatically increase by one.- Hide quoted text - - Show quoted text - Thanks for your help Gord. This code works; however, only when I type the values and not when they're pasted from another application, including Excel. alex |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
plus 1
On Jul 19, 5:14 pm, Gord Dibben <gorddibbATshawDOTca wrote:
I tested before posting and tested again just now. Works one cell at a time as you paste a date to each cell. Are you trying to paste a range of cells? Gord On Thu, 19 Jul 2007 07:18:21 -0700, alex wrote: On Jul 17, 12:15 pm, Gord Dibben <gorddibbATshawDOTca wrote: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Count 1 Then Exit Sub Application.EnableEvents = False With Target .Value = .Value + 1 .NumberFormat = "dd mm yyyy" Application.EnableEvents = True End With End Sub This is sheet event code. Right-click on the sheet tab and copt/paste the code into that sheet module. Gord On Mon, 16 Jul 2007 03:02:12 -0700, alex wrote: Thanks (to both) for your help. Your suggestions obviously worked. Would you happen to know the VBA code to accomplish the same task, without the extra step? I'd like users to simply paste in the date and have said date automatically increase by one.- Hide quoted text - - Show quoted text - Thanks for your help Gord. This code works; however, only when I type the values and not when they're pasted from another application, including Excel. alex- Hide quoted text - - Show quoted text - Gord, I am pasting a range of cells, sometimes hundreds, but only one column. alex |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
plus 1
You don't say which column but how about column A?
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rcell As Range If Target.Column < 1 Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rcell In Selection If rcell.Value < "" Then With rcell .Value = .Value + 1 .NumberFormat = "dd mm yyyy" End With End If Next endit: Application.EnableEvents = True End Sub Gord On Fri, 20 Jul 2007 03:22:29 -0700, alex wrote: Gord, I am pasting a range of cells, sometimes hundreds, but only one column. alex |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
plus 1
On Jul 20, 4:55 pm, Gord Dibben <gorddibbATshawDOTca wrote:
You don't say which column but how about column A? Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rcell As Range If Target.Column < 1 Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rcell In Selection If rcell.Value < "" Then With rcell .Value = .Value + 1 .NumberFormat = "dd mm yyyy" End With End If Next endit: Application.EnableEvents = True End Sub Gord On Fri, 20 Jul 2007 03:22:29 -0700, alex wrote: Gord, I am pasting a range of cells, sometimes hundreds, but only one column. alex- Hide quoted text - - Show quoted text - That worked Gord; thanks for all your help. alex |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
plus 1
Thanks for the feedback
Glad it worked out. Gord On Wed, 25 Jul 2007 10:43:26 -0700, alex wrote: On Jul 20, 4:55 pm, Gord Dibben <gorddibbATshawDOTca wrote: You don't say which column but how about column A? Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rcell As Range If Target.Column < 1 Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rcell In Selection If rcell.Value < "" Then With rcell .Value = .Value + 1 .NumberFormat = "dd mm yyyy" End With End If Next endit: Application.EnableEvents = True End Sub Gord On Fri, 20 Jul 2007 03:22:29 -0700, alex wrote: Gord, I am pasting a range of cells, sometimes hundreds, but only one column. alex- Hide quoted text - - Show quoted text - That worked Gord; thanks for all your help. alex |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|