vba - Application.Match gives type mismatch

Question

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

Answer 1

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.