Sum formula

[Ben E]

Hi All,

I'm looking for a little help.

What I am trying to do is put a sum formula into a cell,
the location of which is variable by row, i.e. if there
are 30 rows of data I would like to put a sum formula in
row 31, if there are 60 row of data , I would like to put
the formula in row 61, the start point in each case will
be the same row 2, and I know where I would like to put
the formula, so far I have the formula:

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

I have tried to put a count function into the formula, ie
I know I what would like to sum the 30 rows above, found
using a counter, but I have no idea how to incorporate it
into the formula, eg to replace the -60 in the above
formula, with the result of the counter.

Any help would be appreciated.

Ben E

 

[Tom Ogilvy]

Assume you will have a cell in the column selected when you run the macro.

Cells(2,activecell.column).End(xldown)(2).FormulaR1C1 = _
"=SUM(R2C:R[-1]C)"

 

[Zbiq]

Sub Sum_of_the_column()
a = ActiveCell.Row
b = ActiveCell.End(xlUp).Row
c = ActiveCell.Column
d = Application.WorksheetFunction.Sum(Range(Cells(a, c), Cells(b, c)))
ActiveCell.Offset(1, 0).Value = d
End Sub
To use this macro you have to stay at the last (not blank) cell of the
summarized column and run it.