Application.Match gives type mismatch

Question

I am trying to use Application.Match , but it returns a type mismatch error:13 . 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 was found. In this case, Application.Match returns an Excel error code, that is, a variant / error whose value is Error 2042 (this corresponds to getting #N/A in Excel).

This error value cannot be forced to bind to String (this is what MsgBox expects), and thus you get a type mismatch.

Note that with WorksheetFunction.Match you can call the same Match function. The only difference is how to handle errors:

• With WorksheetFunction errors are treated as VBA errors that can be used using the On Error syntax.

• Using 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.