Excel VBA Runtime Error "13" Type Mismatch

Question

I created a macro for the file, and at first it worked fine, but today I opened and restarted the file and the macro hundreds of times, and I always get the following error: Excel VBA Runtime Error '13' Type Mismatch

I haven’t changed anything in the macro and don’t know why I am getting an error. In addition, it takes time to update the macro every time I run it (the macro should run about 9000 lines).

The error is 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 am using excel 2010 for windows 7. Thanks a lot

Answer 1

You will get a type mismatch if Sheets(name).Cells(4 + i, 57) contains a non-numeric value. You must check the fields before considering them to be numbers, and try to subtract them.

In addition, you must enable Option Strict , so you will have to explicitly convert the variables before trying to perform type-specific operations with them, such as subtraction. This will help you identify and resolve problems in the future. Unfortunately, Option Strict is for VB.NET only. However, you should find best practices for explicit data type conversions in VBA.

---

Update:

If you are trying to quickly fix your code, wrap the string ** and the next one in the following state:

 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 the expected value in the next iteration.

Answer 2

Thanks guys for your help! Finally, I was able to make it work perfectly thanks to a friend, and also to you! Here is the final code so you can 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 

Answer 3

Diogo

Justin gave you very good advice :)

You will also get this error if the cell in which the calculation is performed has an error derived from the formula.

For example, if cell A1 has # DIV / 0! error, then you will get "Excel VBA runtime error" 13 "Type mismatch" when executing this code

 Sheets("Sheet1").Range("A1").Value - 1 

I made some changes to your code. Could you check it out for me? Copy the code with line numbers as I intentionally placed 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 

Answer 4

I had the same problem as you mentioned above, and my code did fine all day yesterday.

I continued to program this morning, and when I opened the application (my Auto_Open swap file), I got a runtime error "13", I went to the Internet to find answers, I tried a lot of things, modifications, and at some point I I remembered reading somewhere about the "Ghost" data that remains in the cell, even if we don’t see it.

My code only transfers data from one file that I opened earlier to another, and summarize it. My code settled on the third SheetTab (so it did the right thing for the 2 previous SheetTabs, where the same code passed without stopping) with a type mismatch message. And he does it every time on the same SheetTab when I restart my code.

So, I selected the cell where it stopped, manually entered 0.00 (since the type mismatch comes from the summation variables declared in the DIM as Double) and copied this cell in all subsequent cells where the same problem occurred. He solved the problem. There was never a message again. Nothing to do with my code except Ghost or data from the past. This is similar to the fact that if you want to use Control + End and Excel, you will get it once, when you have the data, and delete it. To “Save” and close the file when you want to use Control + End, make sure Excel points you to the right cell.

Answer 5

 Sub HighlightSpecificValue() 'PURPOSE: Highlight all cells containing a specified values Dim fnd As String, FirstFound As String Dim FoundCell As Range, rng As Range Dim myRange As Range, LastCell As Range 'What value do you want to find? fnd = InputBox("I want to hightlight cells containing...", "Highlight") 'End Macro if Cancel Button is Clicked or no Text is Entered If fnd = vbNullString Then Exit Sub Set myRange = ActiveSheet.UsedRange Set LastCell = myRange.Cells(myRange.Cells.Count) enter code here Set FoundCell = myRange.Find(what:=fnd, after:=LastCell) 'Test to see if anything was found If Not FoundCell Is Nothing Then FirstFound = FoundCell.Address Else GoTo NothingFound End If Set rng = FoundCell 'Loop until cycled through all unique finds Do Until FoundCell Is Nothing 'Find next cell with fnd value Set FoundCell = myRange.FindNext(after:=FoundCell) 'Add found cell to rng range variable Set rng = Union(rng, FoundCell) 'Test to see if cycled through to first found cell If FoundCell.Address = FirstFound Then Exit Do Loop 'Highlight Found cells yellow rng.Interior.Color = RGB(255, 255, 0) Dim fnd1 As String fnd1 = "Rah" 'Condition highlighting Set FoundCell = myRange.FindNext(after:=FoundCell) If FoundCell.Value("rah") Then rng.Interior.Color = RGB(255, 0, 0) ElseIf FoundCell.Value("Nav") Then rng.Interior.Color = RGB(0, 0, 255) End If 'Report Out Message MsgBox rng.Cells.Count & " cell(s) were found containing: " & fnd Exit Sub 'Error Handler NothingFound: MsgBox "No cells containing: " & fnd & " were found in this worksheet" End Sub 

Answer 6

This error occurs when the type of the input variable is erroneous. You probably wrote the formula in Cells(4 + i, 57) , which instead of =0 used the formula = "" . Therefore, when you run this error is displayed. Since the empty string is not zero.