Using variable in sum formula

[gsx]

Hi
I start with:

Sub test2()
With ActiveCell
.Formula = "=SUM(" & _
.Offset(-8, 0).Address(0, 0) & ":" & _
.Offset(-1, 0).Address(0, 0) & ")"
End With
End Sub

While this is hardcoded, I would be happy with help, putting in a variabel instead. In my macro it is the first/upper row which is to be set

It could be with reference to:
FirstRow
LastRow or CurrentRegion

Kind regards
Geir

 

[venkat1926]

are you thinking something like this

Sub test()
Dim r As Range, ssum As Double
Set r = Range(Range("A1"), Range("A1").End(xlDown))
ssum = WorksheetFunction.Sum(r)
MsgBox ssum
End Sub

 

[mikerickson]

Perhaps something like

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

might work.

What formula do you want in the ActiveCell?

 

[Peter_SSs]

Geir

You can use a structure like this. Assumes you already have variables FirstRow and LastRow holding the relevant row numbers.

Const FormulaBase As String = "=SUM(R#C:R^C)"
ActiveCell.FormulaR1C1 = Replace(Replace(FormulaBase, "#", FirstRow), "^", LastRow)

Given that the last row for SUM formulas is often the row immediately above the formula cell, if you have that situation then you don't need the LastRow variable and you could use this slightly simpler code.

Const FormulaBase As String = "=SUM(R#C:R[-1]C)"
ActiveCell.FormulaR1C1 = Replace(FormulaBase, "#", FirstRow)

 

[gsx]

Dear all,

thanks for responding!
With special thanks to Peter helping me out on understanding formula syntax.

My code now is as follows:

Sub test3()
Dim FirstRow As Integer
FirstRow = Range("J65536").End(xlUp).Offset(0, 0).End(xlUp).Offset(0, 0).Row
Const FormulaBase As String = "=SUM(R#C:R[-1]C)"
With Range("J65536").End(xlUp).Offset(1, 0)
.FormulaR1C1 = Replace(FormulaBase, "#", FirstRow)
.Font.Bold = True
End With
End Sub

With kind regards,
thanks again

 

[Peter_SSs]

Glad you have it working.

I would offer 4 further suggestions.

1. You have a couple of superfluous Offsets in there. an offset of (0,0) doesn't offset at all so is not required

2. It won't matter for you now, and maybe never will, but Excel now has more than 65,536 rows so I would recommend writing code that will work in any version.

3. Use Long data type instead of Integer. Integer has a limit of 32,767 so wouldn't even cope with your version of Excel if your data filled more than about half the rows. In any case, it is my understanding that vba converts all Integer values to Long values to work with them anyway, so you might as well start out that way.

4. When posting code in the forum, use code tags so that your code can be formatted (indentation). Code that is not indented is much harder to read and debug and potential helpers may just pass over your thread rather than try to decipher the code. See my signature block for code tag help.

So, demonstrating all those things, this would be my version.

Sub test3()
    Dim FirstRow As Long
    
    FirstRow = Range("J" & Rows.Count).End(xlUp).End(xlUp).Row
    Const FormulaBase As String = "=SUM(R#C:R[-1]C)"
    With Range("J" & Rows.Count).End(xlUp).Offset(1, 0)
        .FormulaR1C1 = Replace(FormulaBase, "#", FirstRow)
        .Font.Bold = True
    End With
End Sub

 

[gsx]

Thank you Peter,
for your comment about (vba) logic.

Your final suggestion certainly works as wanted!

I'll take your advice about posting codes to the board

Kind regards
Geir