30 Advanced Excel Tips for 2026

Excel Champs / VBA / VBA Type Mismatch Error (Error 13)
By Puneet Gogia (Microsoft MVP)

VBA Type Mismatch Error (Error 13)

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

Error 13, “Type mismatch”, means VBA got a value it can’t convert to the type it needs. The most common case is reading a cell into a typed variable. With Dim qty As Long, the line qty = Range("A2").Value fails when A2 holds text, an error value such as #N/A, or a formula that returns an empty string.

Check the value before assigning it: If IsNumeric(Range("A2").Value) Then for numbers, If IsDate(Range("A2").Value) Then for dates, and If IsError(Range("A2").Value) Then for error values. Or read the cell into a Variant, which accepts anything, and test that.

Joining text with + causes it too: "Total: " + 100 fails because VBA tries to add the text as a number, so use & to join text. Comparing a cell that holds an error value with anything, as in If Range("A2").Value = 0 Then, also gives error 13.

To find the cause, click Debug on the error message and hover over each variable on the highlighted line, or look at the cell it reads; the value that isn’t the expected type is the culprit.

Type Mismatch (Error 13) occurs when you try to assign a value to a variable that doesn’t match its data type. In VBA, when you declare a variable, you define its data type, and when you specify a value that is different from that data type you get the type mismatch error 13.

type-mismatch-error

In this tutorial, we will see the possible situations where runtime error 13 can occur while executing a code.

Type Mismatch Error with Date

In VBA, there is a specific data type to deal with dates, and sometimes it happens that you are using a variable to store a date, but the value you specify is different.

In the following code, I have declared a variable as a date, and then I have specified the value from cell A1, where I am supposed to have a date only. But as you can see, the date that I have in cell A1 is not in the correct format, so VBA is not able to identify it as a date.

Sub myMacro()
Dim iVal As Date
iVal = Range("A1").Value
End Sub

Type Mismatch Error with Number

You can have the same error while dealing with numbers, when you get a different value while trying to specify a number to a variable.

In the following example, you have an error in cell A6, which is supposed to have a numeric value. So, when you run the code, VBA shows you the runtime 13 error because it cannot identify the value as a number.

Sub myMacro()
Dim iNum As Long
iNum = Range("A6").Value
End Sub

Runtime Error 6 Overflow

In VBA, there are multiple data types to deal with numbers, and each of these data types has a range of numbers that you can assign to it. But there is a problem when you specify a number that is out of the range of the data type.

In that case, VBA shows you runtime error 6 overflow, which indicates that the number you have specified is out of the range and you need to change the data type. For example, a variable declared as Integer can hold numbers only from -32,768 to 32,767, so storing 40,000 in it gives this error, while a Long can hold it. You can learn more about it in Overflow Error 6.

Other Situations When It Can Occur

There might be some other situations in which you could face the runtime error 13: Type Mismatch.

  1. When you assign a range to an array, but that range only consists of a single cell. A single cell returns one value, not an array.
  2. When you define a variable as an object, but in the code, you specify a different object to that variable (for example, a range to a variable declared as Worksheet).
  3. When you specify a variable as a worksheet but use the Sheets collection in the code, and the Sheets collection returns a chart sheet, which isn’t a worksheet.

Type Mismatch Error with an Input Box

An input box always returns text. So, when you store its reply in a number variable and the user types a word, or clicks Cancel (which returns an empty text), VBA shows the type mismatch error. The fix is to store the reply in a String variable first and check it before you use it as a number.

Sub AskForNumber()
Dim userInput As String
Dim qty As Long
userInput = InputBox("Enter the quantity.")
If IsNumeric(userInput) Then
qty = CLng(userInput)
Else
MsgBox "Please enter a number."
End If
End Sub

Check the Value Before You Use It

Most of the time, you can avoid this error by checking the value before you assign it to a variable. VBA has a few functions for this:

  • IsNumeric: Returns True if the value can be used as a number.
  • IsDate: Returns True if the value can be used as a date.
  • IsError: Returns True if the cell has an error value, like #N/A or #DIV/0!.

In the following code, I have used IsError and IsNumeric to check cell A6 before storing its value in the variable. If the cell has an error value or text, you get a message instead of the runtime error.

Sub CheckBeforeAssign()
Dim iNum As Long
If IsError(Range("A6").Value) Then
MsgBox "Cell A6 has an error value."
ElseIf IsNumeric(Range("A6").Value) Then
iNum = Range("A6").Value
Else
MsgBox "Cell A6 doesn't have a number."
End If
End Sub

And for a date, you can use IsDate in the same way:

Sub CheckDateBeforeAssign()
Dim iVal As Date
If IsDate(Range("A1").Value) Then
iVal = Range("A1").Value
Else
MsgBox "Cell A1 doesn't have a valid date."
End If
End Sub

Keep in mind that a conversion function like CLng or CDate doesn’t fix the problem on its own. If the value can’t be converted, the function itself returns the type mismatch error. That’s why the check comes first.

How to Fix Type Mismatch (Error 13)

The best way to deal with this error is to use the On Error GoTo statement to run a specific line of code or show a message box to the user when the error occurs. But you can also check the code step by step before executing it. For this, you need to use VBA’s debug tool (Debug > Step Into), or you can also use the shortcut key F8. You can learn more about it in VBA Error Handling.

Frequently Asked Questions

How do I find which line is causing Error 13?

When the error message shows up, click Debug. VBA highlights the line with the error in yellow. You can then hover over the variables on that line, or use the Locals window, to see which value has the wrong type.

Can a blank cell cause a type mismatch error?

No. When you assign a blank cell to a number variable, VBA stores 0, and a Date variable gets 0 as well (12:00:00 AM). But an empty text, like the reply from an input box when the user clicks Cancel, does cause the error when you store it in a number variable.

Does Option Explicit prevent Error 13?

No. Option Explicit makes you declare every variable, which helps you catch typing mistakes in variable names. But Error 13 is about the value you assign while the code runs, so you still need to check the value with functions like IsNumeric or IsDate.

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel