Dynamic Sum Reference

[MikeG]

I have a macro that includes the following:


============================================
a = Selection.Row
b = Selection.Rows.Count
Cells(a + b, 1).Select

Selection.EntireRow.Insert

column_to_sum = 4

ActiveCell.Formula = "=sum(D1:D3)"

=============================================

What I want to do is make the ActiveCell.Formula line dynamic, so instead of hard-coding D1:D3, I want the column to be based on the value of the column_to_sum variable (currently 4, but could be other) and the rows to be summed to be from the value of variable a to variable a + b -1.

In effect something like =sum(range(Cells(a, column_to_sum), cell(a+b-1, column_to_sum)))

...if you see what I mean.

Thanks,

Mike

 

[JoeMo]

Try:

Dim RngToSum as Range
set RngToSum= range(Cells(a, column_to_sum), cells(a+b-1, column_to_sum))
ActiveCell.Formula = "=sum(" & RngToSum.Address & ")"

 

[MikeG]

Try:

Dim RngToSum as Range
set RngToSum= range(Cells(a, column_to_sum), cells(a+b-1, column_to_sum))
ActiveCell.Formula = "=sum(" & RngToSum.Address & ")"

Thanks JoMo - that's just what I need.

MikeG