CODEWITHSUNDEEP
excelvba
sai krishna
sai krishna

Reputation: 33

How to put sum formula in lastrow through vba

Could you tell me how to put sum formula in last row. I don't want to sum of the values in last row. I tried to explain you with below code.

Sub Sum_Formula_In_Lastrow()
lr = Cells(Rows.Count, 2).End(xlUp).Row
lc = Cells(1, 1).End(xlToRight).Column
For x = 4 To lc
Cells(lr, x).Select
Selection.Formula = "=sum(Range(Cells(x, 2), Cells(lr, x)))"
Next x
End Sub

Please suggest me, i know above code is wrong but i'm trying to explain my requirement.

Upvotes: 0

Views: 2497

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

Your variables and range object need to be outside the quotes and concatenated with &. Also the default of Range is .Value. You want to put the Address through not the values.

Sub Sum_Formula_In_Lastrow()
lr = Cells(Rows.Count, 2).End(xlUp).Row
lc = Cells(1, 1).End(xlToRight).Column
For x = 4 To lc
    Cells(lr, x).Formula = "=SUM(" & Range(Cells(2, x), Cells(lr - 1, x)).Address & ")"
Next x
End Sub

Of course this is exactly what R1C1 is designed for. You can fill the whole row without iterating:

Sub Sum_Formula_In_Lastrow()
lr = Cells(Rows.Count, 2).End(xlUp).Row
lc = Cells(1, 1).End(xlToRight).Column
Range(Cells(lr, 4),Cells(lr,lc)).FormulaR1C1 = "=SUM(R2C:R" & lr -1 & "C)" 

End Sub

One last note you want to qualify the ranges to a sheet even if it is the active sheet. Just in case the sheet gets changed.

Sub Sum_Formula_In_Lastrow()
With ActiveSheet
    lr = .Cells(.Rows.Count, 2).End(xlUp).Row
    lc = .Cells(1, 1).End(xlToRight).Column
    .Range(.Cells(lr, 4),.Cells(lr,lc)).FormulaR1C1 = "=SUM(R2C:R" & lr -1 & "C)" 
End With
End Sub

Upvotes: 1

Related Questions