Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba date XL2000
I am looking for a sheet code that puts tomorrow's date in the cell
bellow today's date after a value is imputed in the row of today's date today is A1 7/12/03 and cell D1 is empty when i put a value in D1 i want tomorrows date in A2 now lets say today is 7/13/03 and D2 is empty and it stays empty then no date will go into A3 now lets say today is 7/14/03 and D2 is empty when i put a value in D2 i want tomorrow's date in A3 now lets say today is 7/15/03 and D3 is empty when i put a value in D3 i want tomorrow's date in A4 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba date XL2000
I want TOMORROW date in a cell below today's date only when a value is place
in a cell next to today's Date If there is no value next to the cell that has today's date then there will be NO tomorrows Date Lets start with A12 and today is Saturday 7/12/03 In B12 There is NO Value Then there is no date in A13 now it is Sunday 7/13/03 and there is still NO Value In B12 Then there is still no date in A13 Now it is Monday 7/14/03 and there is now a Value In B12 Now the date Will be entered into A13 and so on Don Guillett wrote: Taking you literally that you want TOMORROWS date in each cell right click on sheet tabview codeinsert thissave now whenever you have a date, tomorrows date will be put one cell down. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If IsDate(Target) Then Target.Offset(1) = Date + 1 Application.EnableEvents = True End Sub -- Don Guillett SalesAid Software Granite Shoals, TX "~Alan Rosenberg Miami" wrote in message ... I am looking for a sheet code that puts tomorrow's date in the cell bellow today's date after a value is imputed in the row of today's date today is A1 7/12/03 and cell D1 is empty when i put a value in D1 i want tomorrows date in A2 now lets say today is 7/13/03 and D2 is empty and it stays empty then no date will go into A3 now lets say today is 7/14/03 and D2 is empty when i put a value in D2 i want tomorrow's date in A3 now lets say today is 7/15/03 and D3 is empty when i put a value in D3 i want tomorrow's date in A4 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba date XL2000
Tom Thank you
I finally got someone to understand now I need a little tweak The start date is A9 and the input column is C9 I need to skip 2 rows so the next date input will be A12 with the value input C12 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 And Target.Count = 1 Then ' How do you get this line to skip 2 rows If Not IsEmpty(Target) Then Application.EnableEvents = False Cells(Target.Offset(1, 0).Row, 1).Value = Date + 1 ' How do you get this line to skip 2 rows End If End If Application.EnableEvents = True End Sub Tom Ogilvy wrote: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 And Target.Count = 1 Then If Not IsEmpty(Target) Then Application.EnableEvents = False Cells(Target.Offset(1, 0).Row, 1).Value = Date + 1 End If End If Application.EnableEvents = True End Sub Right click on the sheet tab and select view code. paste in the above. Regards, Tom Ogilvy ~Alan Rosenberg Miami wrote in message ... I want TOMORROW date in a cell below today's date only when a value is place in a cell next to today's Date If there is no value next to the cell that has today's date then there will be NO tomorrows Date Lets start with A12 and today is Saturday 7/12/03 In B12 There is NO Value Then there is no date in A13 now it is Sunday 7/13/03 and there is still NO Value In B12 Then there is still no date in A13 Now it is Monday 7/14/03 and there is now a Value In B12 Now the date Will be entered into A13 and so on Don Guillett wrote: Taking you literally that you want TOMORROWS date in each cell right click on sheet tabview codeinsert thissave now whenever you have a date, tomorrows date will be put one cell down. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If IsDate(Target) Then Target.Offset(1) = Date + 1 Application.EnableEvents = True End Sub -- Don Guillett SalesAid Software Granite Shoals, TX "~Alan Rosenberg Miami" wrote in message ... I am looking for a sheet code that puts tomorrow's date in the cell bellow today's date after a value is imputed in the row of today's date today is A1 7/12/03 and cell D1 is empty when i put a value in D1 i want tomorrows date in A2 now lets say today is 7/13/03 and D2 is empty and it stays empty then no date will go into A3 now lets say today is 7/14/03 and D2 is empty when i put a value in D2 i want tomorrow's date in A3 now lets say today is 7/15/03 and D3 is empty when i put a value in D3 i want tomorrow's date in A4 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba date XL2000
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And Target.Count = 1 Then If Target.row mod 3 = 0 and Target.row 8 then If Not IsEmpty(Target) Then Application.EnableEvents = False Cells(Target.Offset(3, 0).Row, 1).Value = Date + 1 End If End if End If Application.EnableEvents = True End Sub or Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 And Target.Count = 1 Then If not(isempty(Cells(Target.row,1))) and Target.row 8 then if isdate(cells(target.row,1)) then If Not IsEmpty(Target) Then Application.EnableEvents = False Cells(Target.Offset(3, 0).Row, 1).Value = Date + 1 End If End If End if End If Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy ~Alan Rosenberg Miami wrote in message ... Tom Thank you I finally got someone to understand now I need a little tweak The start date is A9 and the input column is C9 I need to skip 2 rows so the next date input will be A12 with the value input C12 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 And Target.Count = 1 Then ' How do you get this line to skip 2 rows If Not IsEmpty(Target) Then Application.EnableEvents = False Cells(Target.Offset(1, 0).Row, 1).Value = Date + 1 ' How do you get this line to skip 2 rows End If End If Application.EnableEvents = True End Sub Tom Ogilvy wrote: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 And Target.Count = 1 Then If Not IsEmpty(Target) Then Application.EnableEvents = False Cells(Target.Offset(1, 0).Row, 1).Value = Date + 1 End If End If Application.EnableEvents = True End Sub Right click on the sheet tab and select view code. paste in the above. Regards, Tom Ogilvy ~Alan Rosenberg Miami wrote in message ... I want TOMORROW date in a cell below today's date only when a value is place in a cell next to today's Date If there is no value next to the cell that has today's date then there will be NO tomorrows Date Lets start with A12 and today is Saturday 7/12/03 In B12 There is NO Value Then there is no date in A13 now it is Sunday 7/13/03 and there is still NO Value In B12 Then there is still no date in A13 Now it is Monday 7/14/03 and there is now a Value In B12 Now the date Will be entered into A13 and so on Don Guillett wrote: Taking you literally that you want TOMORROWS date in each cell right click on sheet tabview codeinsert thissave now whenever you have a date, tomorrows date will be put one cell down. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If IsDate(Target) Then Target.Offset(1) = Date + 1 Application.EnableEvents = True End Sub -- Don Guillett SalesAid Software Granite Shoals, TX "~Alan Rosenberg Miami" wrote in message ... I am looking for a sheet code that puts tomorrow's date in the cell bellow today's date after a value is imputed in the row of today's date today is A1 7/12/03 and cell D1 is empty when i put a value in D1 i want tomorrows date in A2 now lets say today is 7/13/03 and D2 is empty and it stays empty then no date will go into A3 now lets say today is 7/14/03 and D2 is empty when i put a value in D2 i want tomorrow's date in A3 now lets say today is 7/15/03 and D3 is empty when i put a value in D3 i want tomorrow's date in A4 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba date XL2000
Thank you thank you thank you
Mr Ogilvy your my XL hero :) This one did it Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 And Target.Count = 1 Then If Target.row mod 3 = 0 and Target.row 8 then If Not IsEmpty(Target) Then Application.EnableEvents = False Cells(Target.Offset(3, 0).Row, 1).Value = Date + 1 End If End if End If Application.EnableEvents = True End Sub Tom Ogilvy wrote: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 And Target.Count = 1 Then If Target.row mod 3 = 0 and Target.row 8 then If Not IsEmpty(Target) Then Application.EnableEvents = False Cells(Target.Offset(3, 0).Row, 1).Value = Date + 1 End If End if End If Application.EnableEvents = True End Sub or Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 And Target.Count = 1 Then If not(isempty(Cells(Target.row,1))) and Target.row 8 then if isdate(cells(target.row,1)) then If Not IsEmpty(Target) Then Application.EnableEvents = False Cells(Target.Offset(3, 0).Row, 1).Value = Date + 1 End If End If End if End If Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy ~Alan Rosenberg Miami wrote in message ... Tom Thank you I finally got someone to understand now I need a little tweak The start date is A9 and the input column is C9 I need to skip 2 rows so the next date input will be A12 with the value input C12 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 And Target.Count = 1 Then ' How do you get this line to skip 2 rows If Not IsEmpty(Target) Then Application.EnableEvents = False Cells(Target.Offset(1, 0).Row, 1).Value = Date + 1 ' How do you get this line to skip 2 rows End If End If Application.EnableEvents = True End Sub Tom Ogilvy wrote: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 And Target.Count = 1 Then If Not IsEmpty(Target) Then Application.EnableEvents = False Cells(Target.Offset(1, 0).Row, 1).Value = Date + 1 End If End If Application.EnableEvents = True End Sub Right click on the sheet tab and select view code. paste in the above. Regards, Tom Ogilvy ~Alan Rosenberg Miami wrote in message ... I want TOMORROW date in a cell below today's date only when a value is place in a cell next to today's Date If there is no value next to the cell that has today's date then there will be NO tomorrows Date Lets start with A12 and today is Saturday 7/12/03 In B12 There is NO Value Then there is no date in A13 now it is Sunday 7/13/03 and there is still NO Value In B12 Then there is still no date in A13 Now it is Monday 7/14/03 and there is now a Value In B12 Now the date Will be entered into A13 and so on Don Guillett wrote: Taking you literally that you want TOMORROWS date in each cell right click on sheet tabview codeinsert thissave now whenever you have a date, tomorrows date will be put one cell down. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If IsDate(Target) Then Target.Offset(1) = Date + 1 Application.EnableEvents = True End Sub -- Don Guillett SalesAid Software Granite Shoals, TX "~Alan Rosenberg Miami" wrote in message ... I am looking for a sheet code that puts tomorrow's date in the cell bellow today's date after a value is imputed in the row of today's date today is A1 7/12/03 and cell D1 is empty when i put a value in D1 i want tomorrows date in A2 now lets say today is 7/13/03 and D2 is empty and it stays empty then no date will go into A3 now lets say today is 7/14/03 and D2 is empty when i put a value in D2 i want tomorrow's date in A3 now lets say today is 7/15/03 and D3 is empty when i put a value in D3 i want tomorrow's date in A4 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XL2000 - Sumproduct Question | Excel Worksheet Functions | |||
XL2000 CF and Comparing Lists | Excel Worksheet Functions | |||
XL2000, CF and List Comparisons | Excel Worksheet Functions | |||
Workday function in XL2000 | Excel Worksheet Functions | |||
XL2000: "Filename is Not Valid" | Excel Discussion (Misc queries) |