Catching error on VBA Match function

[a_ud]

Hi,

I'm using function Match in VBA (for a fuzzy text search) in the line:

Application.WorksheetFunction.Match("*" & Trim(Terms(i)) & "*", ActiveSheet.Range("B1:B250"), 0)

Terms(i) is just a string, the function works fine except that eventually it doesn't find any matches. How could I catch this error using IF?

I've tried this:

If IsError(Application.WorksheetFunction.Match("*" & Trim(Terms(i)) & "*", ActiveSheet.Range("B1:B250"), 0)) Then
            'do something
End If

but again, it throws an execution Debug error. Same for IsNull. IsNA is not available in VBA. Any ideas?

Thanks, a.

 

[gaj104]

Not the biggest fan of using excel workbook specific functions in VBA. Usually there is no reason to use them. So, in your example I would use Find, i.e.

If Not Range("B1:B250").Find("*" & Trim(Terms(i)) & "*") Is Nothing Then
    'match found
Else
    'no match found
End If

 

[a_ud]

I appreciate your answer.

The reason I use Match is that it returns a position in a list (not a text). Therefore you can run a search multiple times with different search terms and then find the most likely cell using function Mode.

No idea on how that would be done with Find, though.

But there must be a way to catch errors with worksheet functions..... ??

 

[tlowry]

If it were "feet to the fire" for WorksheetFunction:

Sub x()
    terms = Array("A", "B", "A", "d")
    For i = 0 To UBound(terms)
        On Local Error Resume Next
        m = Application.[B]WorksheetFunction[/B].Match("*" & Trim(terms(i)) & "*", _
                                    ActiveSheet.Range("B1:B250"), 0)
        If Err <> 0 Then
            Debug.Print "not found: " & terms(i)
            Err.Clear
        Else
            Debug.Print "found: " & terms(i)
        End If
    Next i
End Sub

 

[Norie]

Try this.

Dim res As Variant
 
res = Application.Match("*" & Trim(Terms(i)) & "*", ActiveSheet.Range("B1:B250"), 0)
 
If IsError(res) Then
     MsgBox "No match found"
Else
     MgsBox "Match found in row " & res
End If

 

[a_ud]

Thanks all, I've tried several solutions but Norie's seems to be the one.

Oddly enough, the ONLY difference between her code and mine is/was:
NORIE's (working)
X = Application.Match("*" & Trim(Terms(i)) & "*", ActiveSheet.Range("B1:B250"))

MINE (not working)
X = Application.WorkSheetFunction.Match("*" & Trim(Terms(i)) & "*", ActiveSheet.Range("B1:B250"))

Am I missing something? Aren't we supposed to include the WorkSheetFunction when using Match in VBA (or any other function)??

 

[taurean]

Hi,

I'm using function Match in VBA (for a fuzzy text search) in the line:

Application.WorksheetFunction.Match("*" & Trim(Terms(i)) & "*", ActiveSheet.Range("B1:B250"), 0)

Terms(i) is just a string, the function works fine except that eventually it doesn't find any matches. How could I catch this error using IF?

I've tried this:

If IsError(Application.WorksheetFunction.Match("*" & Trim(Terms(i)) & "*", ActiveSheet.Range("B1:B250"), 0)) Then
            'do something
End If

but again, it throws an execution Debug error. Same for IsNull. IsNA is not available in VBA. Any ideas?

Thanks, a.

I believe you can use:

If IsNumeric(Application.Match("*" & Trim(Terms(i)) & "*", ActiveSheet.Range("B1:B250"), 0)) Then
Debug.Print "Match found!"
End If

 

[RoryA]

For the differences between Application.function_name and application.worksheetfunction.function_name see the Error Handling section on Chip's page here.

 

[Norie]

a ud

You can use workhseet functions in VBA without using WorksheetFunction.

The obvious advantage is that not using WorksheetFunction stops the code error.

I can't remember exactly why/how it works though.

 

[gaj104]

The reason I use Match is that it returns a position in a list (not a text). Therefore you can run a search multiple times with different search terms and then find the most likely cell using function Mode.

A find returns a range. From there you can get any property of that range, i.e. the row (as what match does).

i.e.

If Not Range("B1:B250").Find("*" & Trim(Terms(i)) & "*") Is Nothing Then
    'match found
    Set r = Range("B1:B250").Find("*" & Trim(Terms(i)) & "*")
    Debug.Print r.Row 'etc
Else
    'no match found
End If