7 Methods to Fix Runtime Error 13 Type Mismatch in Excel
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
The Runtime error 13 can also occur if the Excel file is corrupted. In such a case, you can try repairing the file using the Microsoft Excel inbuilt “Open and Repair” utility. Follow these steps:
Step_1: Open your Excel application and click on File > Open > Browse.
Step_2: In the ‘Open’ dialog box, choose the Excel file you want to repair and then click the ‘Arrow’ next to the ‘Open’.
Step_3: Select Open and Repair from the dropdown list.
Step_4: You will see a prompt asking you to repair the file or extract data from it.
Step_5: Click the Repair.
If the “Open and Repair” tool fails to fix the corrupted Excel file, then you can use an advanced Excel repair tool, such as Stellar Repair for Excel. This tool can help you repair severely corrupted Excel files. It can recover all the file components, including macros, cell comments, tables, chart sheets, etc., with 100% integrity.
Conclusion
When using VBA in Excel you may encounter the “Runtime error 13: Type mismatch.” It can occur due to several reasons. You can follow the methods discussed above to fix the error. Sometimes, the type mismatch error can occur if your Excel file is corrupt. In such a case, you can try Excel’s Open and Repair utility to repair the file. If it doesn’t work, then use Stellar Repair for Excel. It is a reliable tool that can help repair severely corrupted Excel files and help in fixing errors.