Type mismatch vba excel ошибка
I created a macro for a file and first it was working fine, but today I've been opening and restarting the file and macro hundreds of times and I'm always getting the following error:
Excel VBA Run-time error '13' Type mismatch
I didn't change anything in the macro and don't know why am I getting the error. Furthermore it takes ages to update the macro every time I put it running (the macro has to run about 9000 rows).
The error is on the line in the between ** **.
I'm using excel 2010 on windows 7.
Probably you are doing an if statement and comparing a string and not using double quotes. Use ="3" not = 3
7 Answers 7
You would get a type mismatch if Sheets(name).Cells(4 + i, 57) contains a non-numeric value. You should validate the fields before you assume they are numbers and try to subtract from them.
Update:
If you are trying to go for the quick fix of your code, however, wrap the ** line and the one following it in the following condition:
Note that your x value may not contain its expected value in the next iteration, however.
There shouldn't be any syntax error. Make sure you put it right after your other If statement and that you have all the parentheses.
Option Strict is not available in VBA for Excel, however, you can chose between these 4: Base, Compare, Explicit and Private.
Thank you guys for all your help! Finally I was able to make it work perfectly thanks to a friend and also you! Here is the final code so you can also see how we solve it.
Justin has given you some very fine tips :)
You will also get that error if the cell where you are performing the calculation has an error resulting from a formula.
I have made some slight changes to your code. Could you please test it for me? Copy the code with the line numbers as I have deliberately put them there.
Since there are 9000 rows, give it sometime. Wait and see what happens. Alternatively if you want change LINE 20 to True instead of False. Application.ScreenUpdating = True and then check it. Does it shown any sign of being alive?
For future readers:
This function was abending in Run-time error '13': Type mismatch
To solve it, I had to do this:
I had the same problem as you mentioned here above and my code was doing great all day yesterday.
I kept on programming this morning and when I opened my application (my file with an Auto_Open sub), I got the Run-time error '13' Type mismatch, I went on the web to find answers, I tried a lot of things, modifications and at one point I remembered that I read somewhere about "Ghost" data that stays in a cell even if we don't see it.
My code do only data transfer from one file I opened previously to another and Sum it. My code stopped at the third SheetTab (So it went right for the 2 previous SheetTab where the same code went without stopping) with the Type mismatch message. And it does that every time at the same SheetTab when I restart my code.
So I selected the cell where it stopped, manually entered 0,00 (Because the Type mismatch comes from a Summation variables declared in a DIM as Double) and copied that cell in all the subsequent cells where the same problem occurred. It solved the problem. Never had the message again. Nothing to do with my code but the "Ghost" or data from the past. It is like when you want to use the Control+End and Excel takes you where you had data once and deleted it. Had to "Save" and close the file when you wanted to use the Control+End to make sure Excel pointed you to the right cell.
Visual Basic может преобразовать и привести большое число значений для присвоений типа данных, которые не были возможны в предыдущих версиях.
Тем не менее, эта ошибка может по-прежнему повторяться и имеет следующие причины и решения:
- Причина:Переменная или свойство имеют неверный тип. Например, переменная целого типа, не может принимать строковые значения, которые не распознаются как целые числа.
Решение: Попробуйте выполнять задания только между совместимыми типами данных. Например, значение типа Integer всегда можно присвоить типу Long, значение Single — типу Double, а любой тип (за исключением пользовательского) — типу Variant.
- Причина: В процедуру, требующую отдельное свойство или значение, передан объект.
Решение: Передайте отдельное свойство или вызовите метод, соответствующий объекту.
Причина: Используется имя модуля или проекта, где требуется выражение, например:
Решение: Укажите выражение, которое будет отображаться.
Причина: Попытка использовать традиционный механизм обработки ошибок Basic со значениями Variant с подтипом Error (10, vbError), например:
Решение: Чтобы воссоздать ошибку, необходимо сопоставить ее с пользовательской или внутренней ошибкой Visual Basic, после чего снова создать ее.
Причина: Значение CVErr не может быть преобразовано в тип Date. Например:
Решение: Используйте оператор Select Case или аналогичную конструкцию, чтобы сопоставить возвращаемое значение CVErr с соответствующим значением.
Решение: Для печати массивов используйте цикл в котором каждый элемент отображается отдельно.
Для получения дополнительной информации выберите необходимый элемент и нажмите клавишу F1 (для Windows) или HELP (для Macintosh).
Хотите создавать решения, которые расширяют возможности Office на разнообразных платформах? Ознакомьтесь с новой моделью надстроек Office. Надстройки Office занимают меньше места по сравнению с надстройками и решениями VSTO, и вы можете создавать их, используя практически любую технологию веб-программирования, например HTML5, JavaScript, CSS3 и XML.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Visual Basic is able to convert and coerce many values to accomplish data type assignments that weren't possible in earlier versions.
However, this error can still occur and has the following causes and solutions:
- Cause: The variable or property isn't of the correct type. For example, a variable that requires an integer value can't accept a string value unless the whole string can be recognized as an integer.
Solution: Try to make assignments only between compatible data types. For example, an Integer can always be assigned to a Long, a Single can always be assigned to a Double, and any type (except a user-defined type) can be assigned to a Variant.
- Cause: An object was passed to a procedure that is expecting a single property or value.
Solution: Pass the appropriate single property or call a method appropriate to the object.
Cause: A module or project name was used where an expression was expected, for example:
Solution: Specify an expression that can be displayed.
Cause: You attempted to mix traditional Basic error handling with Variant values having the Error subtype (10, vbError), for example:
Solution: To regenerate an error, you must map it to an intrinsic Visual Basic or a user-defined error, and then generate that error.
Cause: A CVErr value can't be converted to Date. For example:
Solution: Use a Select Case statement or some similar construct to map the return of CVErr to such a value.
Solution: To print arrays, create a loop that displays each element individually.
For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).
Interested in developing solutions that extend the Office experience across multiple platforms? Check out the new Office Add-ins model. Office Add-ins have a small footprint compared to VSTO Add-ins and solutions, and you can build them by using almost any web programming technology, such as HTML5, JavaScript, CSS3, and XML.
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
A VBA Type Mismatch Error occurs when you try to assign a value between two different variable types.
The error appears as “run-time error 13 – Type mismatch”.
For example, if you try to place text in a Long integer variable or you try to place text in a Date variable.
Let’s look at a concrete example. Imagine we have a variable called Total which is a Long integer.
If we try to place text in the variable we will get the VBA Type mismatch error(i.e. VBA Error 13).
Let’s look at another example. This time we have a variable ReportDate of type Date.
If we try to place a non-date in this variable we will get a VBA Type mismatch error
In general, VBA is very forgiving when you assign the wrong value type to a variable e.g.
However, there are some conversions that VBA cannot do
A simple way to explain a VBA Type mismatch error, is that the items on either side of the equals evaluate to a different type.
When a Type mismatch error occurs it is often not as simple as these examples. For these more complex cases we can use the Debugging tools to help us resolve the error.
VBA Type Mismatch YouTube Video
How to Locate the Type Mismatch Error
The most important thing to do when solving the Type Mismatch error is to, first of all, locate the line with the error and then locate the part of the line that is causing the error.
If your code has Error Handling then it may not be obvious which line has the error.
If the line of code is complex then it may not be obvious which part is causing the error.
The following video will show you how to find the exact piece of code that causes a VBA Error in under a minute:
The following sections show the different ways that the VBA Type Mismatch error can occur.
Assigning a string to a numeric
As we have seen, trying to place text in a numeric variable can lead to the VBA Type mismatch error.
Below are some examples that will cause the error
Invalid date
VBA is very flexible when it comes to assigning a date to a date variable. If you put the month in the wrong order or leave out the day, VBA will still do it’s best to accommodate you.
The following code examples show all the valid ways to assign a date followed by the cases that will cause a VBA Type mismatch error.
Cell Error
A subtle cause of the VBA Type Mismatch error is when you read from a cell that has an error e.g.
If you try to read from this cell you will get a type mismatch error
To resolve this error you can check the cell using IsError as follows.
However, checking all the cells for errors is not feasible and would make your code unwieldy. A better way is to check the sheet for errors first and if errors are found then inform the user.
You can use the following function to do this
The following is an example of using this code
Invalid Cell Data
As we saw, placing an incorrect value type in a variable causes the ‘VBA Type Mismatch’ error. A very common cause is when the value in a cell is not of the correct type.
A user could place text like ‘None’ in a number field not realizing that this will cause a Type mismatch error in the code.
If we read this data into a number variable then we will get a ‘VBA Type Mismatch’ error error.
You can use the following function to check for non numeric cells before you use the data
You can use it like this
Module Name
If you use the Module name in your code this can cause the VBA Type mismatch to occur. However in this case the cause may not be obvious.
For example let’s say you have a Module called ‘Module1’. Running the following code would result in the VBA Type mismatch error.
Different Object Types
So far we have been looking mainly at variables. We normally refer to variables as basic data types.
They are used to store a single value in memory.
In VBA we also have objects which are more complex. Examples are the Workbook, Worksheet, Range and Chart objects.
If we are assigning one of these types we must ensure the item being assigned is the same kind of object. For Example
Sheets Collection
In VBA, the workbook object has two collections – Sheets and Worksheets. There is a very subtle difference
- Worksheets – the collection of worksheets in the Workbook
- Sheets – the collection of worksheets and chart sheets in the Workbook
A chart sheet is created when you move a chart to it’s own sheet by right-clicking on the chart and selecting Move.
If you read the Sheets collection using a Worksheet variable it will work fine if you don’t have a chart sheet in your workbook.
If you do have a chart sheet then you will get the VBA Type mismatch error.
In the following code, a Type mismatch error will appear on the Next sh line if the workbook contains a chart sheet.
Array and Range
You can assign a range to an array and vice versa. In fact this is a very fast way of reading through data.
The problem occurs if your range has only one cell. In this case, VBA does not convert arr to an array.
If you try to use it as an array you will get the Type mismatch error
In this scenario, you can use the IsArray function to check if arr is an array
Conclusion
This concludes the post on the VBA Type mismatch error. If you have a mismatch error that isn’t covered then please let me know in the comments.
What’s Next?
Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.
Related Training: Get full access to the Excel VBA training webinars and all the tutorials.
(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)
I am getting a “Run-time error 13- Type Mismatch” in MS project. When I click debug I get “If version > 12 Then call AddPPMToolRibbon”. Need help please
Check that version is a number
Hi Paul,
I’m getting a mismatch error for a date. I have a table with lots of different ideas with about 5 possible dates (as the ideas go through stages) and first I’m trying to sort the data for easier reading and eventually equations. I have “Dim hopper as Date” and the row that is flagged/highlighted when the error message pops up is “hopper = ActiveCell.Offset(0,23).Value” . The quotation marks are not included in the code. The information grabbed before this date looks right and the column is the right column, but the cell is blank. Is the error because the date is blank? If so, how do I tell the computer to leave the value of hopper for that idea blank?
The date cannot be blank. Check if it the cell = “” and if it does then don’t assign the date.
very useful b structured info. thanks a lot
my problem is, somehow, different.
I do get mismatch errors on Excel, and … I usually fix them.
But my issue is … that something weird happens. No matter what I do (ex. fixing the mismatch), the macros stop running.
I have to, save, close excel, re-open the spreadsheet and then run them again, and … usually they work (if I was able to fix the mismatch. If not, I have to repeat the whole thing over again).
So, what could I do next time, besides closing and re-opening Excel?
Are you turning off events? You could be setting EnableEvents to false. When the code stops then events are turned off. You need to set EnableEvents to True or none of the events will run. You can run Application.EnableEvents = True in the Immediate Window(Ctrl + G).
I’m getting this error:
Specific error= Getting oShape tag attributes.
You need to show the line of code.
hi Paul, I have this error in my excel, but I have no idea how to fix it. reading your article helps me understand a little about the error, but I still have no idea how to fix it. could you please help me to fix this error for me? thank you
If Not Intersect(Target, Range(“C5:C19”)) Is Nothing Then
For n = 0 To 14
Set cellcat = Range(“C5:C19”).Offset(n, 0)
If cellcat.Value = (“”) Or swc = 1 Then – this is the error
cellcat.Interior.Color = RGB(246, 246, 246)
swc = 1
Else
cellcat.Interior.Color = RGB(8, 152, 139)
End If
Next n
hi Paul, I have this error in my excel, but I have no idea how to fix it. reading your article helps me understand a little about the error, but I still have no idea how to fix it. could you please help me to fix this error for me? thank you
If Not Intersect(Target, Range(“C5:C19”)) Is Nothing Then
For n = 0 To 14
Set cellcat = Range(“C5:C19”).Offset(n, 0)
If cellcat.Value = (“”) Or swc = 1 Then – this is the error
cellcat.Interior.Color = RGB(246, 246, 246)
swc = 1
Else
cellcat.Interior.Color = RGB(8, 152, 139)
End If
Next n
Set catRange = Range(“$C$5:” & Range(“C5”).End(xlDown).Address)
nocat = catRange.Rows.Count
‘MsgBox (nocat)
Set catdash = Sheets(“Tablero”).Range(“$N$34:$N$” & 34 + nocat – 1) – this is the error
ThisWorkbook.Names.Add Name:=”categories”, RefersTo:=catRange
ThisWorkbook.Names.Add Name:=”catdash”, RefersTo:=catdash
I'm running a code to create a daily report, first I was working with cells and offsets to compare the date but that was slow, now I'm using arrays to improve the code, but I'm encountering a problem when trying to use some application functions, first I tried VLookup and all I got was Run-time error '13': Type mismatch. Now I'm working with Match and Index methods and I get the same error, I can't seem to find what my error is.
I'm passing a Variant Array, that I get this way:
And the error comes here, when I try to execute the Application.Index method.
I ran only this line Application.Index(wsrgcmes, 0, 1) in the debug window and I got the same error. The Variant Array wsrgcmes contains a table with numeric and string values. Any help I cant get, I appreciate it. Thanks!
Values of wbrgc :
Where is this array declared? If you don't specify a data type or structure then Excel's going to have to "guess".
Add a watch on wsrgcmes to make sure that the array wsrgcmes is populated correctly. Check the value of lRow and lCol also.
It is 1 based array since you are building from range. My immediate guess would be to test with temp = Application.Index(wsrgcmes, 1, 1) .
4 Answers 4
When you get the values of a range to a Variant , it is always a 1-based 2D array:
Thus row index 0 is out of the array bounds, and Application.Index raises a type mismatch error in that case.
From the comment of @Mathieu Guindon:
Both syntaxes are perfectly valid, and each demand different error-handling mechanics (early-bound: On Error. late-bound: IfError(. ))
Thanks for your answer. As suggested before, I tried Debug.Print Application.Index(wsrgcmes, 1, 1) and I keep getting the same mistmatch error. Is there another workaround to this, can't find what I'm doing wrong.
@SebasXHP - select the word wsrgcmes in your VBEdtior, press Shift+F9 and put a screenshot of what you see on the question.
Application.Index has a limit for number of columns and rows. If you exceed this you will hit a type mismatch error. So this might be the problem.
See the following for ways to work around this:
I am not sure what the current limit is. It was more than 65,536 rows or 65,536 columns. The solution in the links is essentially to use arrays to slice rather than Application.Index.
You could completely avoid Index and use TimWilliams helper function .
Using @TimWilliams helper function you would have
Hey! I tried the function you suggested and It actually works. It does what I wanted it to do. Thank you very much. Maybe it's too much to ask, but do your know if there's a way to do that but quicker? Now it's taking kinda long to finish, I know there are a lot of iterations to get done, but if I can get it working faster it would be great. Thanks a lot.
I hope that doesn't feel too much like a re-direct but they are awesome over there at reviewing code and code optimization is their bag. One really needs to see the code in its entirety to start understanding where to make optimizations beyond switching off the usual suspects like screenupdating, calculation mode etc.
I don't believe the issues is caused by INDEX's limitations. As per my answer, INDEX will handle over 1048576 rows, but is limited to 65536 columns. The original issue (i.e. error when printing to immediate window) is entirely down to OP trying to print an array, I believe.
@jeffreyweir Nice to hear from you. Seems like I jumped the gun. Good to know. Where are these limitations listed as I can never remember them? On MSDN? I'll have a look at your answer as this will be good learning for me. Prior comment deleted.
When you feed the INDEX funciton a 0, you're telling it "I want the entire row or column".
If you assign Application.Index(wsrgcmes, 0, 1) to a variant, it works fine. you've got a variant with an array in it.
If you try to print Application.Index(wsrgcmes, 0, 1) to the immediate window, you get an error because you're trying to print an array. You need to wrap it in a JOIN function.
I don't know why you are getting an error on Application.Index(wsrgcmes, 14630, 1) but my guess is that at the time you did it, wsrgcmes wasn't populated or wasn't that dimension. I'd need to see a screenshot of both the exact part of the code you were using when the error occurred as well as a screenshot of the array in the Watch Window.
I don't believe your issue is caused by the limitations of INDEX, because you are nowhere near them.
Читайте также: