Complex date VBA - insert a certain weekday

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

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: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.
 
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?
Click to expand...

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 said:
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)
Click to expand...



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

^_^
 
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)
Click to expand...



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

^_^
 
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 said:
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"
Click to expand...

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
 
Back
Top