SUM several ranges into a total

[cccbzg]

Hi,

I'm trying to do something I thought would be simple, but have had no success. I need to SUM several ranges of numbers in F8:11, F14:20, C23:C28, F23:F26, I23:I31 into one total. The code below adds only F8 through F11 and ignores the rest. Please help!

With Sheets("MEL")
myTOTAL = Worksheets("MEL").Range("F8:F11, F14:F20, C23:C28, F23:F26, I23:I31")
TOTALResults = WorksheetFunction.Sum(myTOTAL)
Range("G4") = TOTALResults
End With

Many thanks,
Bonnie

 

[Jonmo1]

Try

Set myTOTAL = Worksheets("MEL").Range("F8:F11, F14:F20, C23:C28, F23:F26, I23:I31")

 

[cccbzg]

So simple...just 3 letters!

THANKS YOU. Thanks you.

 

[Zack Barresse]

Jonmo1 is assuming the "myTOTAL" variable is a Range object. If it's not you'll need to adjust. Not sure why you need all the variables anyway. You could shorten to...

Range("G4").Value = WorksheetFunction.Sum(Worksheets("MEL").Range("F8:F11, F14:F20, C23:C28, F23:F26, I23:I31"))

 

[Jonmo1]

Jonmo1 is assuming the "myTOTAL" variable is a Range object.

I didn't assume anything.
I simply made a suggestion for a method that would achieve the desired result based on the information available in the post.

 

[Zack Barresse]

If it's not a Range, or other object, then the Set keyword will give a compile error. If, for example, myTOTAL was declared as a Double, it could have been...

myTOTAL = WorksheetFunction.Sum(Worksheets("MEL").Range("F8:F11, F14:F20, C23:C28, F23:F26, I23:I31"))

Since we don't see any variable declarations, sure seems like an assumption to me.

 

[Jonmo1]

The fact that the code works at all

The code below adds only F8 through F11 and ignores the rest. Please help!

Suggests that the variable is either not declared at all, or is declared as a Variant.
If it was a Double, it would get a type mismatch error.

 

[Zack Barresse]

Sure would. Point being there were missing pieces. Not sure why it's worth arguing about.