Repeating macro with dynamic range
 

Good afternoon experts,

I need help with an Excel macro.

My data looks like this:
A B
1
2 5
3 6
4
5 2
6 4
7 10
8

I want a macro to insert the following formulas:
B1 - "= Sum(A2:A3)"
B4 - "= Sum(A5:A7")"
etc.

The result will be:
A B
1 11
2 5
3 6
4 16
5 2
6 4
7 10
8

I recorded the following macro but the range is static so it only works
once:
Sub Totals()
'
' Totals Macro
' Macro recorded 9/30/2005 by DJ
'
Range("S2973").Select
Selection.End(xlUp).Select
Range("T2968").Select
ActiveCell.FormulaR1C1 = "=SUM(R[1]C[-1]:R[5]C[-1])"
End Sub


What VB syntax will make it repeat indefinitely?

Thanks in advance,
Don Jellie

Repeating macro with dynamic range
 

Sub AA()
Dim j As Long, i As Long
Dim lastrow As Long
j = 1
i = 1
lastrow = Cells(Rows.Count, 2).End(xlUp).Row
Do While i <= lastrow + 1
If IsEmpty(Cells(i, 2)) And i < 1 Then
Cells(j, 2).Value = "=sum(A" & j + 1 & ":A" & i - 1 & ")"
j = i
End If
i = i + 1
Loop

End Sub


--
Regards,
Tom Ogilvy


wrote in message
oups.com...
Good afternoon experts,

I need help with an Excel macro.

My data looks like this:
A B
1
2 5
3 6
4
5 2
6 4
7 10
8

I want a macro to insert the following formulas:
B1 - "= Sum(A2:A3)"
B4 - "= Sum(A5:A7")"
etc.

The result will be:
A B
1 11
2 5
3 6
4 16
5 2
6 4
7 10
8

I recorded the following macro but the range is static so it only works
once:
Sub Totals()
'
' Totals Macro
' Macro recorded 9/30/2005 by DJ
'
Range("S2973").Select
Selection.End(xlUp).Select
Range("T2968").Select
ActiveCell.FormulaR1C1 = "=SUM(R[1]C[-1]:R[5]C[-1])"
End Sub


What VB syntax will make it repeat indefinitely?

Thanks in advance,
Don Jellie