worksheetfunction.sum - using defined range

[Blunder1]

Hi,

I need to select a range and call it rng, say a1, e1, g1, and i1 then in vba sum this using the worksheetfunction,sum(rng). However it is only pickihn up the first cell. My code and i'm struggling to correct it.

Thanks in advance

Blunder

Sub sumcells()
Dim r As Integer
Dim rng As Range
r = ActiveCell.Row
rng = Range("a" & r & "," & "e" & r & "," & "g" & r & "," & "i" & r)
ActiveCell.Value = WorksheetFunction.Sum(rng)
End Sub

 

[Misca]

You were so close but forgot to set the range:

Sub sumcells()
Dim r As Integer
Dim rng As Range
r = ActiveCell.Row
Set rng = Range("a" & r & "," & "e" & r & "," & "g" & r & "," & "i" & r)    'notice the "Set"
ActiveCell.Value = WorksheetFunction.Sum(rng)
End Sub

 

[VoG]

Try

Sub sumcells()
Dim r As Integer
Dim rng As Range
r = ActiveCell.Row
Set rng = Range("a" & r & "," & "e" & r & "," & "g" & r & "," & "i" & r)
ActiveCell.Value = WorksheetFunction.Sum(rng)
End Sub

 

[Blunder1]

an hour spent chopping, changing and all i was missign was 'Set'!!!!!

thanks for you fast responses, much appreciated

Blunder