Data type in Excel indicates the kind of data each field holds, such as date, text, number, etc. When users try mixing two different data types, they can get type mismatch errors. One such error is Runtime Error 13 Type Mismatch.
This error can occur due to several reasons. Below I will discuss the possible causes of the Runtime error 13: Type mismatch in Excel and the solutions to resolve the error.
Causes of Type Mismatch Error 13 in Excel
The Runtime error 13 occurs when using VBA in Excel. It can occur due to several reasons, such as:
- Data types are not compatible with VBA.
- Corruption in Excel worksheets.
- Incorrect subtype in a variant of the expression.
- Accidentally clicked a missing function or macro.
- Incorrect expressions/missing expressions in a macro.
- Placing a text in a number variable.
- Accidentally changed the content of the cell.
Methods to Fix Runtime Error 13 – Type Mismatch in Excel
Here are some methods you can try to fix the Type mismatch error in Excel.
Method 1: Fix Incorrect Variable/Property Type
The mismatch error can occur if you are using an incorrect type of variable or property in the VBA code. For example, a variable that requires an integer value never accepts a string value, until the entire string is changed to an integer.
You must use only compatible data types. For example, a Long data type stores an integer. So, if you’re using a string, then change the value of the “String” to an “Integer”.
Method 2: Check the Property or Value of the Procedure/Function
Sometimes, when you pass an object to a procedure that is expecting a single property or value can trigger the type mismatch error in Excel. If this is the case, check and pass the correct single property to a procedure or call a method with the correct object.
Method 3: Check for Incorrect Expressions/Missing Expressions in the Statement
The type mismatch error can also occur if you enter a project name, instead of an expression, in a statement. For example;
VBCopy Debug.Print MyProject
So, check and specify the desired expression that can display on the screen.
Method 4: Use Select Case instead of CVErr Function
Sometimes, Mismatch error 13 in Excel occurs when using the CVerr function in VBA. It usually occurs when you are trying to convert CVErr value to Date.
For example: MyVar = CDate(CVErr(7)).
To resolve the issue, you can use the Select case statement to map the return value of CVErr to another value.
Method 5: Correct the Subtype in a Variant of an Expression
The Runtime error 13 type mismatch can occur when there is an incorrect subtype in a variant in the expression in the code. It also appears if an array is used in a print statement. In such cases, you can create a loop to print arrays so that all elements display individually.
Method 6: Use GoTO<Label> Statement
If the type mismatch error has occurred while using VBA, you can add GoTo EH statement on the top of the code to fix the issue. In Excel, the VBA GoTo statement helps code execution to jump to a specific line within the procedure. When a mismatch error occurs, the execution will jump to a defined line specified by you.
Method 7: Repair the Excel File
If you’re still getting a Runtime error 13, it could be because your Excel file is corrupt. No worries though, you can try to fix it by using Microsoft Excel’s built-in “Open and Repair” tool. Follow these simple steps and you’ll be back to working on your spreadsheet in no time:
Step_1: Open Excel and click on File > Open > Browse.
Step_2: Now browse the file you need to repair from the “Open” window. Once you’ve got it, click on that little ‘Arrow’ next to the ‘Open’ button.
Step_3: Select Open and Repair from the list.
Now, a prompt will pop up giving you two options – Repair or Extract Data.
Step_4: Click the Repair.
Sometimes the “Open and Repair” tool fails to respond or fails to repair the severely damaged .XLS and .XLSX files. In such cases, the best recommendation is to use any third-party Excel repair tool. There are many tools available in the software market for example Stellar Repair for Excel. It is trustable and reliable software that is capable of repairing severely corrupted or damaged Excel files. Moreover, it can recover the major parts of the damaged file without changing the actual formatting.
When using VBA in Excel you may encounter the “Runtime error 13: Type mismatch.” There are many factors that trigger Excel to throw this error. You can implement the methods comprehended above to get rid of the error. Sometimes, the type mismatch error can occur if your Excel files get corrupted. In such a case, you can get the help of Excel’s in-built Open and Repair feature to restore the Excel worksheets. If it doesn’t work, then you can use Stellar Repair for Excel. It is a reliable tool that can help fix the common error that appears due to the severely corrupted Excel files