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.

 

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

^_^

 

[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

 

[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