Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using Formulas in VBA
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using Formulas in VBA
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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Array Formulas to Regular Formulas | Excel Worksheet Functions | |||
E2007 formulas display as formulas, not results | Excel Worksheet Functions | |||
Counting # of Formulas in a column with formulas and entered data | Excel Worksheet Functions | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
AdvancedFilter on cells with formulas, returning values and not formulas | Excel Programming |