I have formulas that i need to copy to fill the end of each of a set of data
ranges. The data is all in A1 to A500, with the ranges seperated by empty
rows. The ranges are of variable length. The formula is required in the A
column just where the blanks appear in the ranges.

Why can't I record a relative address macro that incorporates variable
ranges? I use the end-down-down keys when recording the macro.

Do I have to use a VBA script for something this simple?

I'm sure there's an easier way to do this but here's one way...


Sub Test()
Dim i As Range, iRng As Range
Dim iTop As String, iBot As String

Set iRng = Range("A1:A500")
iTop = "$A$1"

For Each i In iRng
If Len(i) = 0 Then
iBot = i.Offset(-1, 0).Address
i.Formula = "=SUM(" & iTop & ":" & iBot & ")"
iTop = i.Offset(1, 0).Address
End If
Next

End Sub


--
Dan

Thank you Dan. I guess Ineed to get a handle on VBA....



"Dan R." wrote:

I'm sure there's an easier way to do this but here's one way...


Sub Test()
Dim i As Range, iRng As Range
Dim iTop As String, iBot As String

Set iRng = Range("A1:A500")
iTop = "$A$1"

For Each i In iRng
If Len(i) = 0 Then
iBot = i.Offset(-1, 0).Address
i.Formula = "=SUM(" & iTop & ":" & iBot & ")"
iTop = i.Offset(1, 0).Address
End If
Next

End Sub


--
Dan

Here is also a link that helped me:

http://contextures.com/tiptech.html


"Dan R." wrote:

I'm sure there's an easier way to do this but here's one way...


Sub Test()
Dim i As Range, iRng As Range
Dim iTop As String, iBot As String

Set iRng = Range("A1:A500")
iTop = "$A$1"

For Each i In iRng
If Len(i) = 0 Then
iBot = i.Offset(-1, 0).Address
i.Formula = "=SUM(" & iTop & ":" & iBot & ")"
iTop = i.Offset(1, 0).Address
End If
Next

End Sub


--
Dan