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

Answer 1

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.

Answer 2

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