Complex date VBA - insert a certain weekday

[Colin Hayes]

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, 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.

 

[Claus Busch]

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.

 

[Ron Rosenfeld]

.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)

 

[Colin Hayes]

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.

^_^

 

[Colin Hayes]

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.

^_^

 

[Ron Rosenfeld]

OK thanks for these solutions. Both work perfectly first time.

^_^

Glad to help. Thanks for the feedback.

 

[Colin Hayes]

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

 

[Ron Rosenfeld]

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"

 

[Colin Hayes]

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