Total Excel columns from VB
Posted on 2002-04-17
I have written a VB program that creates an EXCEL spreadsheet. I need to SUM columns E and F and G, then total those totals. How do I do that?
I am able to get the last row used by doing:
varRow = Mid(Selection.End(xlDown).Address, 4)
Since I am working with test data and I know how many rows I have, the following formula works:
ActiveCell.FormulaR1C1 = "=SUM(R[-32]C:R[-1]C)"
Obviously, when I use LIVE data, I won’t know what the last row will be, so I want to use my variable in the formula:
ActiveCell.FormulaR1C1 = "=SUM(R[varRow]C:R[-1]C)"
but I get this error:
Run-time error ‘1004’:
Application-defined or object-defined error
Am I close? What do I need to do?
Thanks for your help