Match fails and kills macro

Glory

Well-known Member
Joined
Mar 16, 2011
Messages
640
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"
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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).


Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,050
Messages
5,526,494
Members
409,704
Latest member
saialkesh

This Week's Hot Topics

Top