Excel VBA Run-time error '13' Type mismatch
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 on the line 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
I'm using excel 2010 on windows 7.
Solution 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
Unfortunately 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.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.
Solution 2:
Thank you guys for all your help! Finally I was able to make it work perfectly thanks to a friend and also you! Here is the final code so you can also see how we solve it.
Thanks again!
Option Explicit
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
name = "Reserva"
Sheets(name).Cells(4, 57) = Sheets(name).Cells(4, 56)
On Error GoTo fim
x = Sheets(name).Cells(4, 56).Value
Application.Calculation = xlCalculationManual
Do While Not IsEmpty(Sheets(name).Cells(i + 4, 56))
a = 0
If Sheets(name).Cells(4 + i, 56) <> x Then
If Sheets(name).Cells(4 + i, 56) <> 0 Then
If Sheets(name).Cells(4 + i, 56) = 3 Then
a = x
Sheets(name).Cells(4 + i, 57) = Sheets(name).Cells(4 + i, 56) - x
x = Cells(4 + i, 56) - x
End If
Sheets(name).Cells(4 + i, 57) = Sheets(name).Cells(4 + i, 56) - a
x = Sheets(name).Cells(4 + i, 56) - a
Else
Cells(4 + i, 57) = ""
End If
Else
Cells(4 + i, 57) = ""
End If
i = i + 1
Loop
Application.Calculation = xlCalculationAutomatic
Exit Sub
fim:
MsgBox Err.Description
Application.Calculation = xlCalculationAutomatic
End Sub
Solution 3:
Diogo
Justin has given you some very fine tips :)
You will also get that error if the cell where you are performing the calculation has an error resulting from a formula.
For example if Cell A1 has #DIV/0! error then you will get "Excel VBA Run-time error '13' Type mismatch" when performing this code
Sheets("Sheet1").Range("A1").Value - 1
I have made some slight changes to your code. Could you please test it for me? Copy the code with the line numbers as I have deliberately put them there.
Option Explicit
Sub Sample()
Dim ws As Worksheet
Dim x As Integer, i As Integer, a As Integer, y As Integer
Dim name As String
Dim lastRow As Long
10 On Error GoTo Whoa
20 Application.ScreenUpdating = False
30 name = InputBox("Please insert the name of the sheet")
40 If Len(Trim(name)) = 0 Then Exit Sub
50 Set ws = Sheets(name)
60 With ws
70 If Not IsError(.Range("BE4").Value) Then
80 x = Val(.Range("BE4").Value)
90 Else
100 MsgBox "Please check the value of cell BE4. It seems to have an error"
110 GoTo LetsContinue
120 End If
130 .Range("BF4").Value = x
140 lastRow = .Range("BE" & Rows.Count).End(xlUp).Row
150 For i = 5 To lastRow
160 If IsError(.Range("BE" & i)) Then
170 MsgBox "Please check the value of cell BE" & i & ". It seems to have an error"
180 GoTo LetsContinue
190 End If
200 a = 0: y = Val(.Range("BE" & i))
210 If y <> x Then
220 If y <> 0 Then
230 If y = 3 Then
240 a = x
250 .Range("BF" & i) = Val(.Range("BE" & i)) - x
260 x = Val(.Range("BE" & i)) - x
270 End If
280 .Range("BF" & i) = Val(.Range("BE" & i)) - a
290 x = Val(.Range("BE" & i)) - a
300 Else
310 .Range("BF" & i).ClearContents
320 End If
330 Else
340 .Range("BF" & i).ClearContents
350 End If
360 Next i
370 End With
LetsContinue:
380 Application.ScreenUpdating = True
390 Exit Sub
Whoa:
400 MsgBox "Error Description :" & Err.Description & vbNewLine & _
"Error at line : " & Erl
410 Resume LetsContinue
End Sub
Solution 4:
For future readers:
This function was abending in Run-time error '13': Type mismatch
Function fnIsNumber(Value) As Boolean
fnIsNumber = Evaluate("ISNUMBER(0+""" & Value & """)")
End Function
In my case, the function was failing when it ran into a #DIV/0! or N/A value.
To solve it, I had to do this:
Function fnIsNumber(Value) As Boolean
If CStr(Value) = "Error 2007" Then '<===== This is the important line
fnIsNumber = False
Else
fnIsNumber = Evaluate("ISNUMBER(0+""" & Value & """)")
End If
End Function