Sum formula
 

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

Sum formula
 

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

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

--
Regards,
Tom Ogilvy

"Ben E" wrote in message
...
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

Sum formula
 

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.

"Ben E" wrote in message ...
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