Sum a Dynamic range of cells in the same row with VBA

[nmounir]

Hello Everyone,

I new to VBA and was looking online on how to sum the values in the same row using VBA, i only found on the same column but nothing for the same row.
Cell C2 = 20, Cell D2 = 30, Cell E2 = 40 etc. More values can be added to to cell F2 and G2 etc.

I used the following code but is not working:

Sub Test()

Dim LastCell As Integer
Dim I As Long
LastCell = Range("C2").End(xlToRight).Column
For I = 2 To LastCell
Range("B13").Value = Excel.WorksheetFunction.Sum(Range("C2 & I"))

Next I

End Sub

Any help?

 

[Fluff]

How about

Sub nmounir()
   Range("B13").Value = Application.Sum(Range(Range("C2"), Cells(2, Columns.Count).End(xlToLeft)))
End Sub

 

[nmounir]

How about

Sub nmounir()
   Range("B13").Value = Application.Sum(Range(Range("C2"), Cells(2, Columns.Count).End(xlToLeft)))
End Sub

Fluff,

you are genius. Thanks a million

I tried this code earlier on but also didn't work. any idea what could be wrong with it?
the current region in the immediate window is giving me the correct address which is $C$2:$I$2 then i just put Sum(rng)


Sub Sum()
Dim Rng As String
Rng = Range("C2").CurrentRegion.Address
Range("B13").Value = Excel.WorksheetFunction.Sum(Rng)

End Sub

 

[Fluff]

You need to pass the range object, not the address like

Sub Sum()
Dim Rng As Range
Set Rng = Range("C2").CurrentRegion
Range("B13").Value = WorksheetFunction.Sum(Rng)

End Sub

 

[nmounir]

You need to pass the range object, not the address like

Sub Sum()
Dim Rng As Range
Set Rng = Range("C2").CurrentRegion
Range("B13").Value = WorksheetFunction.Sum(Rng)

End Sub

Thank you so much Fluff. Much appreciated

 

[Fluff]

You're welcome & thanks for the feedback.