# Index ranges by 1

Folks,
In my worksheet Range A4 = Range H2
Range H2 = Today()
I would like to be able to increase ranges A5 = A9 by A4+1
In other words if the date in A4 = 11/26/2013 the the dates in A5:A9 would be incremented by 1 day such that A5 = 11/27/2013...
###### Who is Participating?

x

Commented:
You can replace all of that with:

``````With Range("B4")
.FormulaR1C1 = "=RC[-1]+1"
.AutoFill Destination:=Range("B4:H4"), Type:=xlFillDefault
End With
``````
so that you eliminate the use of Select (which is known to be inefficient and redundant).
0

Commented:
Try:

in A5:

=\$A\$4+ROWS(\$A\$5:\$A5)

copied down
0

Author Commented:
Whoops...my mistake. I'm not going down...going across. I'll try substitute Column for Row?
Also need in VBA format
0

Commented:
The formula would be:

=\$A\$4+COLUMNS(\$A\$5:A\$5)

simple macro:

``````Sub CummulativeDate()
For i = 2 To 6
Cells(4, i) = Cells(4, 1) + i - 1
Next i

End Sub
``````
0

Author Commented:
Here's the code I'm using in my procedure:

``````Dim NextCell As Long
Range("A4") = Range("H2")
For NextCell = 2 To 8
Cells(4, NextCell) = Cells(4, 1) + NextCell - 1
Next NextCell
``````
What happens is that the first cell (B4) is correctly populated, but then I drop completly out of the sub to another sub and never get to the NextCell
0

Author Commented:
I recorded a macro then copy the below code to my subprocedure and it worked.
``````Range("B4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+1"
Range("B4").Select
Selection.AutoFill Destination:=Range("B4:H4"), Type:=xlFillDefault
Range("B4:H4").Select
``````
Can I award points for being "close"?
0

Author Commented:
good job! thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.