how to catch error in VBA when we use Match function

IF Match function cannot detect the value in generates and error . In VBA I tried to catch this error use IsError() function but it can't detected the match error . how can I catch this Matching error in VBA?

Dim X As Long
Dim rng As Range
X = 35
Set rng = ActiveWorkbook.Worksheets(1).Range("D1:D6")
If IsError(WorksheetFunction.Match(X, rng, 0)) Then
  Debug.Print "ERR"
  Debug.Print WorksheetFunction.Match(X, rng, 0)
End If


Question Info

Last updated March 31, 2020 Views 13,498 Applies to:

Use Application.Match instead of WorksheetFunction.Match. Application.Match doesn't cause an error message but returns an error value if there is no match. So:


If IsError(Application.Match(X, rng, 0)) Then

Kind regards, HansV

16 people were helped by this reply


Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.