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...
Frank FreeseAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
NBVCConnect With a Mentor Commented:
You can replace all of that with:

With Range("B4")
    .FormulaR1C1 = "=RC[-1]+1"
    .AutoFill Destination:=Range("B4:H4"), Type:=xlFillDefault
End With

Open in new window

so that you eliminate the use of Select (which is known to be inefficient and redundant).
0
 
NBVCCommented:
Try:

in A5:

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

copied down
0
 
Frank FreeseAuthor Commented:
Whoops...my mistake. I'm not going down...going across. I'll try substitute Column for Row?
Also need in VBA format
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
NBVCCommented:
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

Open in new window

0
 
Frank FreeseAuthor 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

Open in new window

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
 
Frank FreeseAuthor 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

Open in new window

Can I award points for being "close"?
0
 
Frank FreeseAuthor 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.

All Courses

From novice to tech pro — start learning today.