Excel VBA Run-time error '13' Type mismatch

Question

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 9000 rows).

The error is in the between ** **.

VBA:

Sub k()

Dim x As Integer, i As Integer, a As Integer
Dim name As String
name = InputBox("Please insert the name of the sheet")
i = 1
Sheets(name).Cells(4, 58) = Sheets(name).Cells(4, 57)
x = Sheets(name).Cells(4, 57).Value
Do While Not IsEmpty(Sheets(name).Cells(i + 4, 57))
    a = 0
    If Sheets(name).Cells(4 + i, 57) <> x Then
        If Sheets(name).Cells(4 + i, 57) <> 0 Then
            If Sheets(name).Cells(4 + i, 57) = 3 Then
                a = x
                Sheets(name).Cells(4 + i, 58) = Sheets(name).Cells(4 + i, 57) - x
                x = Cells(4 + i, 57) - x
            End If
            **Sheets(name).Cells(4 + i, 58) = Sheets(name).Cells(4 + i, 57) - a**
            x = Sheets(name).Cells(4 + i, 57) - a
        Else
        Cells(4 + i, 58) = ""
        End If
    Else
    Cells(4 + i, 58) = ""
    End If

i = i + 1
Loop

End Sub

Do you think you can help me? I'm using excel 2010 on windows 7. Thanks a lot

Answer 1

You would get a type mismatch if Sheets(name).Cells(4 + i, 57) contains a non-numeric value. You should validate the fields before you assume they are numbers and try to subtract from them.

Also, you should enable Option Strict so you are forced to explicitly convert your variables before trying to perform type-dependent operations on them such as subtraction. That will help you identify and eliminate issues in the future, too.    Unfortunately Option Strict is for VB.NET only. Still, you should look up best practices for explicit data type conversions in VBA.

---

Update:

If you are trying to go for the quick fix of your code, however, wrap the ** line and the one following it in the following condition:

If IsNumeric(Sheets(name).Cells(4 + i, 57))
    Sheets(name).Cells(4 + i, 58) = Sheets(name).Cells(4 + i, 57) - a
    x = Sheets(name).Cells(4 + i, 57) - a
End If

Note that your x value may not contain its expected value in the next iteration, however.