Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex date VBA - insert a certain weekday
Hi All I use this code to place the current date (dd mmm yyyy) in a range of cells when content is added to cells adjacent to them : Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) On Error GoTo enditall Application.EnableEvents = False If Intersect(Range(Target(1).Address), _ Range("B:B, D:D, F:F, H:H, J:J, L:L")) _ Is Nothing Then GoTo enditall With Target If .Value < "" Then With .Offset(0, -1) .Value = Date + 1 '.Columns.AutoFit End With Else: .Offset(0, -1).Value = "" End If End With enditall: Application.EnableEvents = True End Sub Is it possible to have this place the next Tuesday's date (dd mmm yyyy) in the cells , instead of the current date? Can someone assist with an adaptation of the code? Grateful for any advice. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex date VBA - insert a certain weekday
Hi Colin,
Am Tue, 23 Jul 2013 17:29:34 +0100 schrieb Colin Hayes: With Target If .Value < "" Then With .Offset(0, -1) .Value = Date + 1 '.Columns.AutoFit End With Else: .Offset(0, -1).Value = "" End If End With Is it possible to have this place the next Tuesday's date (dd mmm yyyy) in the cells , instead of the current date? try: If Target.Value < "" Then With WorksheetFunction Target.Offset(0, -1) = Date + _ .Choose(.Weekday(Date), 2, 1, 7, 6, 5, 4, 3) End With '.Columns.AutoFit Else: Target.Offset(0, -1).Value = "" End If Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex date VBA - insert a certain weekday
On Tue, 23 Jul 2013 17:29:34 +0100, Colin Hayes wrote:
.Value = Date + 1 I note this is putting in tomorrow's date, not today's date but for next Tuesday, change to: ..Value = Date + 8 - Weekday(Date + 5) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex date VBA - insert a certain weekday
In article , Ron Rosenfeld
writes On Tue, 23 Jul 2013 17:29:34 +0100, Colin Hayes wrote: .Value = Date + 1 I note this is putting in tomorrow's date, not today's date but for next Tuesday, change to: .Value = Date + 8 - Weekday(Date + 5) OK thanks for these solutions. Both work perfectly first time. ^_^ |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex date VBA - insert a certain weekday
On Tue, 23 Jul 2013 17:29:34 +0100, Colin Hayes wrote: .Value = Date + 1 I note this is putting in tomorrow's date, not today's date but for next Tuesday, change to: .Value = Date + 8 - Weekday(Date + 5) OK thanks for these solutions. Both work perfectly first time. ^_^ |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex date VBA - insert a certain weekday
On Wed, 24 Jul 2013 02:04:47 +0100, Colin Hayes wrote:
On Tue, 23 Jul 2013 17:29:34 +0100, Colin Hayes wrote: .Value = Date + 1 I note this is putting in tomorrow's date, not today's date but for next Tuesday, change to: .Value = Date + 8 - Weekday(Date + 5) OK thanks for these solutions. Both work perfectly first time. ^_^ Glad to help. Thanks for the feedback. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex date VBA - insert a certain weekday
In article , Ron Rosenfeld
writes On Wed, 24 Jul 2013 02:04:47 +0100, Colin Hayes wrote: On Tue, 23 Jul 2013 17:29:34 +0100, Colin Hayes wrote: .Value = Date + 1 I note this is putting in tomorrow's date, not today's date but for next Tuesday, change to: .Value = Date + 8 - Weekday(Date + 5) Hi Just a small follow-up to this. I used the code Value = Date + 8 - Weekday(Date + 5) to give an output of 30 Jul 2013 When I added some text to the code : ..Value = "Tuesday " & Date + 8 - Weekday(Date + 5) the output becomes Tuesday 30/07/2013. It seems to have lost the previous formatting. Is it possible to have it read Tuesday 30 Jul 2103 , do you know? Thanks again Best Wishes |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex date VBA - insert a certain weekday
On Wed, 24 Jul 2013 19:00:52 +0100, Colin Hayes wrote:
In article , Ron Rosenfeld writes On Wed, 24 Jul 2013 02:04:47 +0100, Colin Hayes wrote: On Tue, 23 Jul 2013 17:29:34 +0100, Colin Hayes wrote: .Value = Date + 1 I note this is putting in tomorrow's date, not today's date but for next Tuesday, change to: .Value = Date + 8 - Weekday(Date + 5) Hi Just a small follow-up to this. I used the code Value = Date + 8 - Weekday(Date + 5) to give an output of 30 Jul 2013 When I added some text to the code : .Value = "Tuesday " & Date + 8 - Weekday(Date + 5) the output becomes Tuesday 30/07/2013. It seems to have lost the previous formatting. Is it possible to have it read Tuesday 30 Jul 2103 , do you know? Thanks again Best Wishes By adding the text string, you have converted the entire value into text, so the date being displayed is probably your windows regional short date (that's just a guess). If you want to ensure the date is displayed in the cell how you want, you should format the cell appropriately. e.g. ... .value = Date + 8 - weekday(Date+5) .numberformat = "dddd dd mmm yyyy" |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex date VBA - insert a certain weekday
In article , Ron Rosenfeld
writes By adding the text string, you have converted the entire value into text, so the date being displayed is probably your windows regional short date (that's just a guess). If you want to ensure the date is displayed in the cell how you want, you should format the cell appropriately. e.g. ... .value = Date + 8 - weekday(Date+5) .numberformat = "dddd dd mmm yyyy" Hi Ron OK thanks for that. That's fixed it in the desired format. The cell now curiously refuses to wrap the text for some reason , but I'll continue to fiddle with it so it's not too wide for the other cells in the sheet. Best Wishes |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to compute date of next weekday? | Excel Discussion (Misc queries) | |||
convert weekday date to the next sunday date | Excel Worksheet Functions | |||
WEEKDAY() function: display TEXT not numeric weekday | Excel Discussion (Misc queries) | |||
MATCHING DATE TO A WEEKDAY | Excel Worksheet Functions | |||
Getting weekday for a date | Excel Programming |