Unable to get the Match Property of the WorksheetFunction class error

[wtrimble]

I'm getting the error: "Unable to get the Match Property of the WorksheetFunction class" and I'm not sure why. I've seen several other posts with this issue, however have not been able to find a solution. I attempted to use an IsError Function to see if the Match function just wasn't finding the string in the range, however when I get this error message after it reads the Iserror line. I'm using a specific serach "balloki" just to simplify the function but will be using the n() strings. When I CTRL + F "Balloki" in the worksheet, it does find it, so I'm confused. It does find the range, when I do range1.select in the code. Please help!

Private Sub CommandButton1_Click()
On Error GoTo errmsg
ActiveWorkbook.Sheets(1).Activate
Dim n(3) As String
Dim t As Integer
Dim rownum As Integer
Dim range1 As Range
Dim name1 As String
Set range1 = ActiveWorkbook.Sheets(1).Range("A1", "P300")
t = 0
n(0) = "Baglan Bay"
n(1) = "balloki"
n(2) = "barcelona"
n(3) = "BoZ, Bergen op Zoom"
 
name1 = "balloki"
range1.Select
 
If IsError(Application.WorksheetFunction.Match(name1, range1)) Then
MsgBox "yes"
Else
MsgBox "no"
End If
 
Do While t < 31
 
rownum = Application.WorksheetFunction.Match(name1, range1, 0)
MsgBox rownum
MsgBox "after response"
t = t + 1
Loop
UserForm1.Hide
endloop:
End
 
errmsg:
MsgBox Err.Description
GoTo endloop
 
End Sub

 

[Norie]

One problem with the match is that you are using a column with multiple columns (A1:P300).

That wouldn't work on a worksheet either.

You could try this which only uses A1:A300 for the lookup range.

If IsError(Application.WorksheetFunction.Match(name1, range1.Columns(1), 0)) Then

It might be better though to assign the result of the Match to a variable then check the variable.

Dim res As Variant
 
     res  = Application.Match(name1, range1.Columns(1), 0)
 
    If IsError(res) Then
         Msgbox "Yes"
    Else
 
        Msgbox "No"
    End If

You need to remove the WorksheetFunction part to deal with it being an error.

Not 100% sure the technical reason for that but I'm sure it's been explained elsewhere on the forum.

 

[wtrimble]

Figured out the issue, thank you. When I changed the range to a single column it worked. Thanks for your help!