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? |
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? |
All times are GMT +1. The time now is 09:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com