How to store the sum of a range of cells in a variable?

Question

I am trying to store the sum of a range of cells in a variable.

Sub obtainhours()

Dim Total As Integer

Worksheets("22012017").Activate
Total = WorksheetFunction.Sum(Range("D2,D50"))

End Sub

Welcome to SO. You don't need to Activate your sheet for this purpose. Your mistake is you use "," instead of ":". Try this `Total = WorksheetFunction.Sum(Worksheets("22012017").Range("D2:D50"))` — Tehscript, Apr 30 '17 at 14:08

score 0 · Answer 1 · edited May 23 '17 at 12:02

First, you should declare Total As Long, since it's able to store numeric values larger than Integer (>32767).

Also Why use Long instead of Integer

You should avoid using Activate as it slows down your code's run-time, instead use fully qualified object, by implementing the With statement.

Sub obtainhours()

Dim Total As Long

With Worksheets("22012017")
    ' Option 1: get the Sum of cells "D2" and "D50" only
    Total = WorksheetFunction.Sum(.Range("D2,D50"))

    ' Option 2: get the Sum of cells "D2 through cell "D50"
    Total = WorksheetFunction.Sum(.Range("D2:D50"))
End With

End Sub

How to store the sum of a range of cells in a variable?

1 Answers1