Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Application.Match gives type mismatch

Tags:

excel

vba

I am trying to use Application.Match however it is returning a type mismatch error:13 error. Why?

Dim mySrs as Series
Dim ws as Worksheet
set ws Activesheet
For Each mySrs in ActiveChart.SeriesCollection
tempvar = mySrs.Name
y = Application.Match(tempvar, ws.Range("P37:P71"), 0)
MsgBox y
like image 859
As3adTintin Avatar asked Dec 04 '14 19:12

As3adTintin


People also ask

How do I fix mismatch error?

To solve this issue, simply close out of the game and restart the client, which should prompt you to download the latest version. If you aren't prompted, you might need to redownload the game or run the installer again to get the newest update loaded onto your computer.

What type of error is type mismatch?

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

How do I fix type mismatch error in VBA?

Step 1: Write the subprocedure for VBA Type Mismatch. Step 2: Again assign a new variable, let's say “A” as Byte data type. Let's understand the Byte Data type here. Byte can only store the numerical value from 0 to 255.

Is type mismatch a runtime error?

VBA Type Mismatch Explained 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.


1 Answers

In all likelihood, no match is found. In such a case, Application.Match returns an Excel error code i.e. a Variant/Error whose value is Error 2042 (this corresponds to getting #N/A in Excel).

Such an Error value cannot be implicitly coerced to a String (which is what MsgBox expects) and thus you get the type mismatch.

Note that the same Match function can be called using WorksheetFunction.Match. The only difference is how errors are to be handled:

  • With WorksheetFunction, errors are treated as VBA errors, trappable using the On Error syntax.

  • With Application, they return an Excel error code wrapped in a Variant. You can use IsError to see if the returned variable is an Error type variant.

like image 89
Jean-François Corbett Avatar answered Sep 29 '22 11:09

Jean-François Corbett