Using a Variable in a Formula

B

Bob

I want to modify the following formula to use a variable
as the row reference:

ActiveCell.FormulaR1C1 = "=SUM(R[-139]C:R[-1]C)"

The variable is declared as Dim Bottom as Integer

The value is set as
Bottom = ActiveCell.Row

Can someone tell me the syntax to use in the formula to
use "Bottom" in place of "139"? Everything I've tried has
failed.
 
M

Myrna Larson

ActiveCell.FormulaR1C1 = "=SUM(R[-" & Bottom & "]C:R[-1]C)"

or

F = "=SUM(R[-###]C:R[-1]C)"
ActiveCell.Formula = Replace(F, "###", Bottom)
 
Top