VBA Error 13: Type mismatch

Question

Can anyone help me? (I am new in VBA and i also got help to create this macro)

I created a macro for a file and first it was working fine, but today I've been opening and restarting the file and macro hundreds of times and I'm always getting the following error: Excel VBA Run-time error '13' Type mismatch

I didn't change anything in the macro and don't know why am I getting the error. Furthermore it takes ages to update the macro every time I put it running (the macro has to run about 700 rows).

The error is in the between ** **.

VBA:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim j As Integer
Dim z(8) As Integer

Set ws = ThisWorkbook.ActiveSheet

For i = 6 To ws.Cells.SpecialCells(xlCellTypeLastCell).Row - 3
    If Not ws.Rows(i).Hidden = True Then
        For j = 0 To 8
            If Not ws.Cells(i, j + 5) = "" Then
               ** z(j) = z(j) + ws.Cells(i, j + 5) **
            End If
        Next j
    End If
Next i

Application.EnableEvents = False
For j = 0 To 8
    ws.Cells(ws.Cells.SpecialCells(xlCellTypeLastCell).Row - 2, j + 5) = z(j)
Next j
Application.EnableEvents = True
End Sub

Ralph · Accepted Answer · 2016-06-06 12:22:37Z

2

ws.Cells(i, j + 5) at a particular point does not contain a value that can be added to z(j). It's probably blank or doesn't contain a number.

One fix would be to write

On Error Resume Next 'switch off error handling
z(j) = z(j) + ws.Cells(i, j + 5)
On Error Goto 0 'the idiomatic way of switching the default error handling back on

Effectively then, your macro will not attempt to add a non-numeric value to z(j).

But solving the problem this way is a bit like using a sledgehammer to crack a nut: perhaps it's worth investigating the contents of the cell that's causing the error, and programming around that more elegantly.

Also, I question why this needs to be in VBA in the first place. Really you're doing something that excel can do in normal calculation. (Consider SUMIF and SUMIFS) Failing that, it would be quicker if you used a VBA Function rather than responding to change events.

edited Jun 6, 2016 at 12:22

Ralph

9,4544 gold badges35 silver badges42 bronze badges

answered Jun 6, 2016 at 11:41

Bathsheba

235k35 gold badges376 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user5889203 Over a year ago

VBA custom formulas are easier to maintain.

Bathsheba Over a year ago

Workbooks with macros are harder to maintain.

user5889203 Over a year ago

No, formulas can reach a level of complexity that are slightly less complex than the rules for dragon poker. it is MUCH easier to maintain a simple custom formula, than a nested if formula, seven statements deep.

Bathsheba Over a year ago

@RichardU: Indeed. But introducing VBA into a worksheet requires careful consideration. I'm not convinced that is the case here, and complex worksheet formulas can often be broken up. For some reason, many folk dislike having cells dedicated to holding intermediate results.

user5889203 Over a year ago

VBA is not a plague, complex formulas however, can be. I've maintained both. VBA is explicit, and if something breaks, you only need to fix it in one place.

Ralph · Accepted Answer · 2016-06-06 11:51:08Z

In addition to what @Bathsheba mentioned, you might also want to consider declaring variables before using them to avoid problems. I wasn't able to run your code because Excel wouldn't know how to handle Z() and thought of it as a UDF. Try the following and let me know if this solves the problem:

Option Base 0
Option Explicit

Sub tmpTest()

Dim ws As Worksheet
Dim i As Long, j As Integer
Dim z(0 To 8) As Double

Set ws = ThisWorkbook.ActiveSheet

For i = 6 To ws.Cells.SpecialCells(xlCellTypeLastCell).Row - 3
    If Not ws.Rows(i).Hidden = True Then
        For j = 0 To 8
            If Not ws.Cells(i, j + 5) = "" Then
               z(j) = z(j) + IIf(VarType(ws.Cells(i, j + 5).Value) = vbError, 0, ws.Cells(i, j + 5).Value)
            End If
        Next j
    End If
Next i

Application.EnableEvents = False
For j = 0 To 8
    ws.Cells(ws.Cells.SpecialCells(xlCellTypeLastCell).Row - 2, j + 5) = z(j)
Next j
Application.EnableEvents = True

End Sub

Collectives™ on Stack Overflow

VBA Error 13: Type mismatch

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related