Autosum Cells VBA Excel

Question

I am trying to auto-recognize values ​​inside a cell block in Excel using VBA. I kept getting a type mismatch error while debugging the following code. Where am I wrong?

Sub autosumtest() Dim total As Integer Worksheets("Sheet1Test").Select Range("F16:G20").Select total = CInt("=SUM(Selection.Values)") MsgBox (total) End Sub 

EDIT 1: Here is a simple example of my test data called Autosum Range:

Answer 1

You can use [] to evaluate any expression in a spreadsheet.

 Sub autosumtest() Dim total As Integer total = CInt([=sum(sheet1Test!F16:G20)]) MsgBox (total) End Sub 

Answer 2

Your F16 to G20 range will contain different formats. Make sure they have Number formatting and the problem is resolved.

You can also use the following:

 Sub autosumtest() Dim total As Integer total = [=SUM(Sheet1Test!F16:G20)] MsgBox (total) End Sub 

Answer 3

Yes, there is a type mismatch, since "= SUM (F16: G20)" is a string.

The worksheet function should be used to summarize the required range. If there is no desire for something else with a choice, .Select is not required.

 Option Explicit Sub autosumtest() Dim total As Integer Dim rng As Range Set rng = Worksheets("Sheet1Test").Range("F16:G20") total = CInt(WorksheetFunction.Sum(rng)) MsgBox (total) End Sub 

Answer 4

Well, given the length of some other answers and the redundant CInt ....

 Sub Easy() MsgBox Application.Sum(Sheets("Sheet1Test").Range("F16:G20")) End Sub