Update Date Cell

[terilad]

Hi All,

I have the following macro in excel.

Sub Macro()
Dim lngRow As Long
Dim intTemp As Integer
Dim arrData(17) As Variant
arrData(0) = Range("A35")
For lngRow = 3 To 35 Step 2
intTemp = intTemp + 1
arrData(intTemp) = Range("A" & lngRow)
Range("A" & lngRow) = arrData(intTemp - 1)
Next
Range("A1") = varValue
End Sub

This to to rotate names through a 17 week shift rosta when macro is run.

What I need is I have a week commencing date in cell A10, e.g. 04 May 2009
is there a code I can add so that when the shift rotates when I run the macro
the date in Cell A10 can be replaced by the new week commencing date e.g. 11
May 2009
and so on when the macro is run.

Any help is greatfully appreciated.

Many thanks

Terilad

 

[Jacob Skaria]

Try the below. Note the new line added to increment the date. Range("A10") =
Range("A10") + 7

Sub Macro()
Dim lngRow As Long
Dim intTemp As Integer
Dim arrData(17) As Variant

Range("A10") = Range("A10") + 7
arrData(0) = Range("A35")

For lngRow = 3 To 35 Step 2
intTemp = intTemp + 1
arrData(intTemp) = Range("A" & lngRow)
Range("A" & lngRow) = arrData(intTemp - 1)
Next
Range("A1") = varValue
End Sub

If this post helps click Yes

 

[terilad]

Your a genius.

Many thanks

Terilad

Try the below. Note the new line added to increment the date. Range("A10") =
Range("A10") + 7

Sub Macro()
Dim lngRow As Long
Dim intTemp As Integer
Dim arrData(17) As Variant

Range("A10") = Range("A10") + 7
arrData(0) = Range("A35")

For lngRow = 3 To 35 Step 2
intTemp = intTemp + 1
arrData(intTemp) = Range("A" & lngRow)
Range("A" & lngRow) = arrData(intTemp - 1)
Next
Range("A1") = varValue
End Sub

If this post helps click Yes

 

[Jacob Skaria]

Thanks for the feedback..

 

[terilad]

Hi Jacob,

Sorry to pester you but I have noticed another range of cells which require
to go forward 7 days, the code you gave me works fine for the week commencing
cell, the other cells are Days of week cells displayed as Manday 04 and so
on. I have moved the week comm cell data to cell N2, the other 7 cells which
require to go forward 7 days are: D4,F4,H4,J4,L4,N4 and P4 these cells hold
days of week with date, displayed like Monday 04, Tuesday 05 and so on.

Can you give me any help on this.

Regards

Terilad