Fix VBA Run-Time Error 13 (Type Mismatch) in Excel COUNTA
Question details
The user is encountering a Run-time error 13 (Type mismatch) when running an Excel VBA script that uses the COUNTA function to find the next available row.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Using a VBA procedure to calculate the next empty row in a database worksheet so that new data can be appended dynamically.
- Observed behavior
- Instead of returning the next row number, the macro stops execution and throws a 'Run-time error 13: Type mismatch' message.
Verify that your target worksheet name in the workbook exactly matches the sheet name referenced in your VBA script (e.g., 'Database'), as an incorrect or missing sheet name is a common trigger for reference evaluation errors.
Correct the COUNTA Syntax with Bracket Evaluation
Fix the syntax error causing the type mismatch by ensuring you have a complete column reference and a closing parenthesis inside the bracket evaluate method.
When using the shorthand bracket evaluation `[]` in VBA to execute worksheet functions, any missing characters or incomplete references will cause a Type Mismatch error because VBA fails to evaluate the expression as a valid number.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications Editor, and locate the module containing your script.
Find the line of code where you assign the row number using COUNTA. It is likely missing a closing parenthesis or has an incomplete column reference.
Change the line of code to: `iRow = [COUNTA(Database!A:A)] + 1`. Make sure 'Database' matches your actual worksheet name.
Press F5 or run your procedure again to confirm that the Run-time error 13 is resolved and the row calculates properly.
Use the End(xlUp) Method Instead of COUNTA
Replace the COUNTA method with the End(xlUp) property, which is the most reliable standard in VBA for finding the last used row in a dataset.
Resolve VBA Script Errors with WPS Spreadsheet
WPS Spreadsheet provides excellent support for VBA macros and scripts. You can smoothly run, debug, and edit your existing Excel macros using its built-in Developer environment while avoiding compatibility issues.
- 1. Open your macro workbook: Launch WPS Spreadsheet and open the file containing your script.
- 2. Access the Visual Basic Editor: Click on the 'Developer' tab in the top ribbon and select 'Visual Basic' to view your code.
- 3. Apply the robust code fix: Locate the COUNTA error and update it to the `End(xlUp)` method to ensure your macro runs flawlessly.
Frequently Asked Questions
What causes Run-time error 13 'Type mismatch' in VBA?
This error occurs when your code tries to assign a value to a variable that doesn't match its declared data type, or when a worksheet function evaluation (like using brackets `[]`) fails due to incorrect syntax and returns an error instead of an expected numeric value.
Why shouldn't I use COUNTA to find the last row in a VBA macro?
COUNTA simply counts the number of non-empty cells in a range. If your data contains blank cells in the middle of a column, the total count will be less than the actual last row number. This causes the macro to identify the wrong row and overwrite existing data.
Can I run my Excel VBA macros in WPS Office?
Yes, WPS Office fully supports VBA macros. You can open your `.xlsm` files, execute existing macros, and modify your code using the built-in VBA editor found under the Developer tab.
