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.

 

[RoryA]

Except find doesn't always work...

 

[gaj104]

Hi Rorya,

Curious, what circumstances would it fail? Be interested to know, as I use that code in a lot in much of my procedures.

 

[RoryA]

Well, as written, your code is missing a lot of arguments and since the arguments are retained between Find operations in a session (either in code or in the UI) that is dangerous.
Also, if you specify Lookin:=xlvalues, you won't be able to find in hidden cells.

 

[gaj104]

Thats fine, I'm aware of that. Was on the fly code, all my normal code is fully arguemented

 

[tlowry]

There are also some data caused problems ie "" and Null and "*"

Try the code with:

terms = Array("", "xA", "B", "A", "d", Null, "*")

Good to know about the arguments. I presumed that they were default values, if not set.