How to catch an error with Application.WorksheetFunction.Match and handle it?

Question

I'm using the Application.match function to look for the column number of the column heading I'm looking for. I want a customized pop up MsgBox if none of the headings are a match.

Before it gets to the IfError statement, Excel stops my program and shows a MsgBox that says there was no match

col_num = Application.WorksheetFunction.Match("wanted name", theDataWS.Rows(1), 0)

If IsError(col_num) Then 
    MsgBox "Please rename your column name to 'wanted name'"
End If

BigBen · Accepted Answer · 2019-02-09T01:06:02.930

5

One option is to use Application.Match instead of Application.WorksheetFunction.Match.

Application.WorksheetFunction.Match will throw a run-time error if there is no match. You could trap and handle it with an On Error... statement if you wanted, or perhaps better in this case, use Application.Match.
Application.Match will not throw a run-time error; instead, the variable col_num will hold the error value (in this case Error 2042 corresponding to #N/A). Thus you can then test IsError(col_num).*

*Note that in this case col_num should be a Variant (declared either implicitly with Dim col_num or explicitly with Dim col_num As Variant).

edited Feb 09 '19 at 01:06

answered Feb 09 '19 at 00:14

BigBen

46,229
7
24
40

1

You might mention that *col_num* needs to be a variant in order to catch the error. – user11026105 Feb 09 '19 at 00:55
@user11026105 yeah, I had in my wording at some point but didn't include it for whatever reason. – BigBen Feb 09 '19 at 00:58

score 1 · Answer 2 · answered Feb 09 '19 at 00:35

To use Application.WorhsheetFunction.Match that might not find a match, you need to use an Error Handler.

Something like

On Error Resume Next
col_num = Application.WorksheetFunction.Match("wanted name", theDataWS.Rows(1), 0)
If Err.Number <> 0 Then
    On Error Goto 0 ' restore error handling as soon as possible
    MsgBox "Please rename your column name to 'wanted name'"
Else
    On Error Goto 0
    ' non-error code
End If

Or use Application.Match as others have answered

How to catch an error with Application.WorksheetFunction.Match and handle it?

2 Answers2