Increase range size by one cell each run

[rmoon]

I'm trying to write a code that will increase the size of a range that is being summed by one cell each time I run the macro.

Example: SUM(B6:F6) will turn into SUM(B6:G6) after one run of the macro, etc. I've tried recording and using relative references but that didn't work out.

Here's the first run of the code I was trying:

Range("O5").Select
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=+SUM(RC[-13]:RC[-9])"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A5"), Type:= _
xlFillDefault
ActiveCell.Range("A1:A5").Select

Is there a way to get this concept to work?

Thanks!

[SamT]

This will only work for one session of Excel at a time. IOW, It always start with the formula = "=SUM(B6:F6)"

Sub SamT_Static()
Static Iteration As Long
With Range("O5")
  .FormulaR1C1 = "=+SUM(RC[-13]:RC[-9 + Iteration])"
  .Resize(5, 1).FillDown
End With
Iteration = Iteration + 1
End Sub

I have to go, but if you need a dynamic sub, this is the planning I have done for a sub the will continue working across sessions.

Sub SamT_Dynamic()
''' Get last Address in Formula

'''' Get Column# of LastAddress
'If Column number = 14 Then Exit sub

''''Get Address Cells(6, ColNo + 1)
'Strip "$"

''''NewFormula = "=SUM(B6:" & NewAddress & ")"

'''' Range"O6".Formula = NewFormula
'Fill Down

End Sub

.