Enter SUM formula using VBA

[General Ledger]

Dear All,

I am trying to put a formula into a cell using VBA but am having trouble with the range of cells. I am using Excel 2003.

I have a column of amounts with some blanks mixed in. I know my first cell in the range is always row 2 of the column of the current active cell. I know the last cell in the range is always cell directly above the current active cell. How do I pass this range to get VBA to enter a formula?

For example, column C has amounts. Cell C1 has a label. Cell C2 is the beginning of my range, which may or may not have an amount. The active cell is C100. In the active cell, I want to use VBA to enter the formula =SUM(C2:C99) If the active cell is C345, I want the formula to be =SUM(C2:C344)

I tried to use ActiveCell.FormualR1C1 = "=SUM(R[]C[]:R[]C[])" However, I can not figure out how to enter/show the beginning cell of the range, which is always the current column row 2. The ending cell reference would be R[-1]C[0]

Please help make this Holiday Season joyfull.

Thanks,

General Ledger

 

[Domenic]

Try...

ActiveCell.FormulaR1C1 = "=SUM(R2C:R[-1]C)"

 

[Scott Huish]

If you actually want relative addresses, perhaps:

Sub test()
Dim x As Long, y As Long
x = ActiveCell.Column: y = ActiveCell.Row
ActiveCell.Formula = "=SUM(" & Cells(2, x).Address(0, 0) & ":" & Cells(y - 1, x).Address(0, 0) & ")"
End Sub

 

[General Ledger]

Domenic: Your answer worked perfectly!!

Hot Pepper: Your answer is a great example of how to string together several elements to get the desired formula. This helped me to see how far you can go push the envelope on this topic.

Best regards to you both,

General Ledger