Thread: Match Function Type Mismatch

Originally Posted by officialhopsof

I was actually just trying to figure out how to check if no match at all was found, Colin, you mentioned that you wouldn't use the above way due to its multiple calls to Application.Match, is there a more robust solution to this?

Sure, you can do it using the same methodology as snb suggested but using an If...Else...End If block rather than IIf().
They're both robust, but the former only calls Application.Match() once so that's how I'd do it. But if you want to use IIf() then I'm certainly not going to stop you!

Sub ImportData()
    
    Dim vResult As Variant
    
    vResult = Application.Match("FOO", Range("A1:A10"), 0)
    
    If IsError(vResult) Then
        MsgBox "match not found"
    Else
        MsgBox "match found: " & CStr(vResult)
    End If
    
    
End Sub

No difference in robustness involved...

Depending on what you want to achieve ( a test or a result):

Maybe countif is more appropriate, because the result will always be the same vartype:

Sub tst()
  msgbox = Application.CountIf([A1:A10], "ccc") & " times found"
End Sub

If you prefer 'match' you can use it's variable vartype:

Sub tst()
  msgbox if(VarType(Application.Match("ccc", [A1:A10], 0))=10,"not ","") & "found"
End Sub