Update Date Cell

T

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
 
J

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
 
T

terilad

Your a genius.

Many thanks

Terilad

Jacob Skaria said:
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
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cell Data Update 8
Save as 13
VBA Help 3
Automation works with Excel 2003 but NOT Excel 2007 5
Importing Text 2
Remove Identical words 0
Variables not showing value 6
Cell data change 7

Top