Using Sum Function in VBA

[leocar28]

Hi,

I have a small code that pastes in a formula to sum the cell through to P2 as long as there is a valve in the current cell. It loops for all values in the array. It works fine but instead of pasting the formula, is there a way of completing the sum function in VBA and only pasting the calculated value? Essentially it is creating a cumulative tally in column Q of the values in column P only if there is a value in column P. In the code startrow begins at 2 and loops with the total row count in the sheet

If Cells(startrow, "P") <> "" Then
Cells(startrow, "Q").Formula = "=SUM(P2:P" & startrow & ")"
End If

Cheers

 

[AlphaFrog]

Sub Running_Totals()
    Dim v As Variant, i As Long, tot As Double
    With Range("P2", Range("P" & Rows.Count).End(xlUp))
        v = .Value
        For i = 1 To UBound(v)
            If v(i, 1) <> "" Then
                tot = tot + v(i, 1)
                v(i, 1) = tot
            End If
        Next i
        .Offset(0, 1).Value = v
    End With
End Sub

 

[Lian]

How about to use Application.Sum(range())
For example in your code:

If Cells(startrow, "P") <> "" Then
Cells(startrow, "Q")=APPLICATION.SUM(Range("P2:P" & startrow ))
End If

 

[leocar28]

Cells(startrow, "Q")=APPLICATION.SUM(Range(P2:P" & startrow & ")")

I was after something simple like that but I keep getting a compile error.

 

[Lian]

I was after something simple like that but I keep getting a compile error.
View attachment 33359

Hi, I have updated the code as follow:
Cells(startrow, "Q")=APPLICATION.SUM(Range("P2:P" & startrow ))
I forgot to revise your .formula code in details.

 

[leocar28]

Hi, I have updated the code as follow:
Cells(startrow, "Q")=APPLICATION.SUM(Range("P2:P" & startrow ))
I forgot to revise your .formula code in details.

Perfect, thanks.