VBA code to sum values on two different columns and result in another column

[senthil.ok28]

Hi friends!

I need a VBA code to sum values of two columns and result in another column. Please see the example sheet below,

Data1 data2 data3 total
5 6 11
4 5 9
7 6 9 22

Can any give me VBA code istead of the formula as =IF(COUNT(A9:C9),SUM(A9:C9),""). Please give a VBA programming code.
thank you for your assistance in advance.

Thanks & Regards;
Senthil Kumar

[alansidman]

Give this a try:

Sub total()
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("D2:D" & lr).FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"

End Sub

[senthil.ok28]

Thank you alan.

This helps. However it would be great if you could avoid showing the formula in the Excel sheet. Need a dynamic VBA code that only shows the result using for loop. Example for i = 2 to ..

[alansidman]

Try this on for size:

Sub total()
    Dim MyRange As Range
    Dim i As Long
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lr

    With Worksheets("Sheet1")
      Set MyRange = .Range(.Cells(i, 1), .Cells(i, 3))
      .Cells(i, 4) = WorksheetFunction.Sum(MyRange)
    End With
Next i
    

End Sub

[senthil.ok28]

Oh! That was great.Thank you

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.