Match fails and kills macro

[Glory]

a = Application.WorksheetFunction.Match("a", Range("J1:J10"))
MsgBox a

I was just going to do if isempty(a) later on, but it turns out it doesn't just leave the variable empty if there's no match... it errors out.

Is there a simple way to get around this problem without an error handler?

Error: "Unable to get the match property of the worksheet function class"

 

[hiker95]

Glory,


You are missing one part of the Match Function:

match_type Optional. The number -1, 0, or 1. The match_type argument specifies how Excel matches


This:
a = Application.Match("a", Range("J1:J10"))

Should be:

a = Application.Match("a", Range("J1:J10"), 0)




Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub Test()

Dim a As Long

a = 0
On Error Resume Next
a = Application.Match("a", Range("J1:J10"), 0)
On Error GoTo 0
If a = 0 Then
  MsgBox "'a' was not found in range J1:J10"
Else
  MsgBox a
End If

End Sub