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"
Else
  Debug.Print WorksheetFunction.Match(X, rng, 0)
End If

 

Question Info


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

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
www.eileenslounge.com

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.