Excel VBA Sum up/Add values together, even those with commas/decimal

Question

I have a problem where I can't add numbers with decimals. Only numbers with no decimals.

I have written a code to sum up values from different cells. This work fine as long as the numbers are without decimals.

Here is my code:

Sub SumValues()

'This makro is made to add values together depending on 
'x amount of Sheets in the workbook:
Application.ScreenUpdating = False

'A will sum up the values from each cell, 
'depending on the amount of Sheets  in the this Workbook:
A = 0

For I = 1 To ThisWorkbook.Sheets.Count
    'Adding the values from cell E5 to Cells(5, 5 + (I - 1) * 3), 
    'with the distance 3 between each cell:
    A = A + Cells(5, 5 + (I - 1) * 3)
Next I

'The values A that is added togheter from the cells, is now put in a cell:
Worksheets("Sheet1").Cells(1, 1).Formula = "=" & A & ""
Application.ScreenUpdating = True

End Sub

So for 3 number of sheets, "I" goes from 1 to 3. So if my cells contain these numbers:

Cell(5,5) = 2

Cell(5,8) = 3

Cell(5,11) = 8

I get the sum in Cell(1,1) = 13

But if I have these values:

Cell(5,5) = 2,2

Cell(5,8) = 3

Cell(5,11) = 8

I get the "run-time error '9': Subscript out of range" Error message when running script. Any suggestions?

Another question is if it is possible to get the formula into the cell I am adding up the values? For Examlpe if I have 3 Sheets in my Workbook, it will sum up the values from Cell(5,5) , Cell(5,8) and Cell(5,11). The sum is shown in Cell(1,1). But all I get is the number, not the formula. Is it possible to make the Cell show the formula "=E5+H5+K5"? This last question might be a "fix" for the first question, if it is the separator "," that is making trouble, maybe?

Thanks

GingerBoy

Answer 1

Tested and working fine

1. Declare your variables
2. You need to qualify your objects with a worksheet
3. No need to toggle off Screen Updating here. You are just modifying one cell

This code will place the Value in A1 and the Formula in B1

---

Disclaimer:

Your code, and the code below, is subject to a potential Type Mismatch Error if you feed any cell with a non-numerical value into your loop. If there is some chance of any non-numerical cell being in the sum range, you can avoid the error by nesting something like the following inside your loop: If ISNUMERIC(Range) Then.

---

Sub SumValues()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim A As Double, i As Long, Loc As String

For i = 1 To ThisWorkbook.Sheets.Count
    A = A + ws.Cells(5, (5 + (i - 1) * 3))
    Loc = ws.Cells(5, (5 + (i - 1) * 3)).Address(False, False) & "+" & Loc
Next i

ws.Range("A1") = A
ws.Range("B1").Formula = "=" & Mid(Loc, 1, Len(Loc) - 1)

End Sub