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.

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

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)

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.

^_^

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.

^_^

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.

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

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"

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