Loop problem in VBA _error 13

[Clom]

Hi! I have a problem with an error 13 (whatever that is) regarding a loop within VBA. My office is 2013.

I have a workbook with a group of sheets, named more or less like this:

DataSheet - Sheet1 - Sheet2 - Sheet3 - Sheet4 - Sheet5 - CALC - [and more sheets after CALC]

Between Sheet1 and Sheet5 I have a formula in each H55 cell (or Cells(55,8) to make some calculations and retrieve a decimal number (like 67.3). Some of this cells may get no value because the formula within H55 will only make the calculations if certain conditions are met. So, sometimes H55 may show a number in Sheet1 but maybe Sheet2 nothing will show in H55.

Now to the code. The code is to sum all the H55 values between Sheet1 and Sheet5 (from the second to the sixth)

Sub whatever()
    Dim i as Byte     'this is for the Next For loop
    Dim sumUp as Double  'I tried as double, variant, etc.
    sumUp = 0   'this is to have just a starting value 
    
    For i = 2 to 6
       sumUp = sumUp + Worksheets(i).Cells(55, 8).Value
    Next i
   
   Worksheets(CALC).Cells(43, 4).Value = sumUp

End Sub

And thats it! I get a debug error, error 13. Even if I put some if statement to ignore empty cells, the error will keep:

If Application.WorksheetFunction.CountA(Worksheets(i).Cells(55, 8)) > 0 Then [this is right before the sumUp = sumUp + etc]

Can you help me? Thanks!

[MrShorty]

13 is a type mismatch error. When H55 shows "nothing" what exactly is in H55? I would guess the formula in H55 is returning some kind of non-printing character (null string or space or something). Of course, VBA does not know how to add a number and a text string, so you get the error.

I see a few possible solutions:

1) Are you required to use VBA for this? Excel's SUM() function can support 3D ranges and knows how to automatically ignore text. CALC!D43 could be a simple formula like =SUM('Sheet1:Sheet5'!H55). No need for VBA for this part of the project.
2) Use the COUNT() function instead of the COUNTA() function. COUNTA() counts all non-empty cells, whether they contain number or text or non-printing characters. COUNT() only counts numeric values and ignores text.
If Application.WorksheetFunction.Count(Worksheets(i).Cells(55, 8)) > 0 Then [this is right before the sumUp = sumUp + etc]
A similar strategy would be to use VBA's IsNumeric() function.
=If IsNumeric(Worksheet(i)...) Then [this is right before the sumUp = sumUp + etc]


Would either of those strategies work for you?

[Clom]

Hi! Thank you very much! I will try all of you solutions! You gave some ideas I never tought about. About using an excel formula instead VBA, the problem is that sometimes new sheets may be added between Sheet1 and Sheet5 and they will also be used to calculation (thats the reason for looping through the worksheets). In fact, my loop is not exactly i = 2 to 5. Instead of a 5 I have a variable (but thats not the problem for my question lol). I am saying this just to show you the need for vba. Unless excel formulas for sum through sheets, can take into account a dynamic number sheets (I am really just a casual user of excel, so I dont know if thats the case!).

Now, I will test your ideas and then I will feedback here. Do I need to change something later like saying "solution verified"?

Thank you once again for your amazing tips!

[Clom]

It worked! The solution was so simple indeed: Count() instead of CountA, thats it! Sometimes one feels dumb just by seeing that the solution was really simple lol. Thank you very much!
By the way, I didnt checked the other solutions, since the one you gave was the fastest (just remove the "A" from COUNTA lol).

Thank you very much!

[MrShorty]

If you are unfamiliar with 3D referencing, this help file discusses how Excel works with 3D references: https://support.microsoft.com/en-us/...es_in_formulas

For your purposes, one feature is that a 3D reference will refer to the first sheet, the last sheet, and any sheets in between. As you copy/move/insert/delete sheets in between the first and last sheet, the 3D reference will update to include (or not) whatever is between the first and last sheet. In the given example, as long as the first sheet is named "Sheet1" and the last sheet is named "Sheet5," then the formula should have not trouble with the number of sheets in between or what their names are.

Reading between the lines, you may intend for the last sheet to change names. When there are 6 sheets it will be Sheet5, but when there are 8 sheets, it will be Sheet7. In order to handle this, some like to introduce two blank sheets with consistent sheet names around their desired 3D range. Maybe a blank sheet named "startsum" before the first sheet in the 3D reference, and a second blank sheet named "endsum" after the last sheet in the 3D reference. Then the formula would be =SUM('startsum:endsum'!H55). Now there is no need to manipulate the names of the sheets in the 3D reference if they might change, because any sheet you put in between those two "fixed" sheets will be included.

The only thing I might add is that sometimes this kind of 3D referencing is really trying to cover for poor database design. For example, someone will have monthly income statements where each sheet in the file is its own month, and they want to summarize the year's income by summing across the months. This is usually considered a poor database design. I would usually be preferable to build a good database with all of the months' information in a single sheet. Good database design makes it much easier and more efficient to summarize information (using pivot tables or summaryIFS() type functions. If your happy with your spreadsheet design, then ignore this. If what you are doing is more like database work, there could be value in considering the design of your database and consider whether a different design would be better.

[Clom]

Always learning! Very useful stuff indeed! I know excel for years (for casual use within the context of my job and sometimes personal use) but I never heard about the 3d referencing, thanks for sharing that!!