using Formulas in VBA

[Alan M]

I need to be able to add the sum of a column of data into the cell at the
bottom of the column. I can do this using

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


However this number of rows in the column may vary so I cannot use a
pre-defined R1C1 range in the formula. Each time the code is run the number
of rows may be more or less so the -121 will vary.

How can I overcome this please?

[Bob Phillips]

This will calculate the last used row in column A and put the formula in the
next row

Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).row
Cells(iLastRow + 1, "A").FormulaR1C1 = "=SUM(R[-" & iLastRow &
"]C:R[-1]C)"


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Alan M" <[email protected]> wrote in message
news:[email protected]...
> I need to be able to add the sum of a column of data into the cell at the
> bottom of the column. I can do this using
>
> ActiveCell.FormulaR1C1 = "=SUM(R[-121)]C:R[-1]C)"
>
>
> However this number of rows in the column may vary so I cannot use a
> pre-defined R1C1 range in the formula. Each time the code is run the
number
> of rows may be more or less so the -121 will vary.
>
> How can I overcome this please?