Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |