How to Set Offset Ranges Dynamically?

Posted by Joe on July 04, 2001 8:55 PM

I need to sum several sections of columns in a spreadssheet.
The number of rows to sum varies from spreadsheet to spreadsheet.

The formula I've tried to use is this:
ActiveCell.FormulaR1C1 = "=SUM(R[Start]C:R[End]C)"
where Start and End are the offsets to the row numbers
to be summed. I get error # 1004.

This formula works just fine, but uses literals for the offsets:
ActiveCell.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"

How can I use the variables in the offset formula?

Thanks

Posted by Ivan F Moala on July 04, 2001 11:41 PM

ActiveCell.FormulaR1C1 = "=SUM(R[" & START & "]C:R[" & END & "]C)"

Ivan
Note: Not tested

Posted by Joe on July 05, 2001 8:00 PM

Thanks Ivan. Your formula worked just fine.
I had difficulty until I realized the spaces
you have between the "&" and the variable are
not optional. They got to be present.