Type mismatch error when using match on an array in Excel VBA

Question

This code works fine

Dim MyArray

MyArray = Array("fee", "fi", "fo", "fum")

If Application.Match("fee", MyArray, 0) Then
   Debug.Print "Pass"
End If

However, this code throws a type mismatch error on the if statement

Dim MyArray

MyArray = Array("fee", "fi", "fo", "fum")

If Application.Match("foo", MyArray, 0) Then
   Debug.Print "Pass"
End If

What am I missing?

learn.microsoft.com/en-us/office/vba/api/… Match returns the #N/A error if there is no match. VBA is kicking back a type mismatch because an IF statement is looking for a boolean value it can't parse the returned error. — Warcupine
– Warcupine, Commented Jul 17 at 15:07
Ok... I probably learned that years ago and forgot... Thank you — XCELLGUY
– XCELLGUY, Commented Jul 17 at 15:09
If IsNumeric(Application.Match("foo", MyArray, 0)) Then or If Not IsError(Application.Match("foo", MyArray, 0)) Then — VBasic2008
– VBasic2008, Commented Jul 17 at 15:20
There is no IsNA in VBA. I prefer IsNumeric in this case, but it's all the same because the result can only be an integer (whole number) or a #N/A error. — VBasic2008
– VBasic2008, Commented Jul 17 at 15:33
Note that if performance is important then a simple loop-based function can be faster when your array of values is not on a worksheet: stackoverflow.com/a/18769246/478884 — Tim Williams
– Tim Williams, Commented Jul 17 at 19:10

VBasic2008 · Accepted Answer · 2025-07-17 16:56:56Z

`Application.Match`

The following two usually work the same:

If IsNumeric(Application.Match("foo", MyArray, 0)) Then
If Not IsError(Application.Match("foo", MyArray, 0)) Then

If you pass correct parameters, the result can only be an integer (whole number) or a #N/A error.
You shouldn't be paranoid about a different error than the #N/A error (Error 2042 in VBA).
If you can't help it, here's something to play with.

Sub Test()
    
    ' Uncomment or out-comment any of the following lines before 'Get the index.'
    ' and observe the behavior.
    
    On Error GoTo ClearError
    
    Dim MyArray As Variant
    'Dim MyArray() As Variant
    'Dim MyArray() As String
    'Dim MyArray As String
    
    MyArray = Array("fee", "fi", "fo", "fum")
    
    ' Get the index.
    Dim Index As Variant: Index = Application.Match("fee", MyArray, 0)
    
    ' Display the result.
    If IsNumeric(Index) Then
        Debug.Print "Pass: Index = " & Index
    Else
        If Index = CVErr(xlErrNA) Then ' xlErrNA = 2042
            Debug.Print "No match"
        Else
            Debug.Print "Something unexpected happened (" & CStr(Index) & ")!"
        End If
    End If

ProcExit:
    Exit Sub
ClearError:
    MsgBox "Run-time error " & "[" & Err.Number & "]:" & vbLf & vbLf _
        & Err.Description, vbCritical
    Resume ProcExit
End Sub

rotabor · Accepted Answer · 2025-07-17 16:47:57Z

0

Use XMATCH for the unsorted array.

Use CVErr function with 'xlErr*' constants to detect errors.

Dim MyArray

MyArray = Array("fo", "fee", "fum", "fi")

If Application.XMatch("fee", MyArray) <> CVErr(xlErrNA) Then
   Debug.Print "Pass"
End If

answered Jul 17 at 16:47

rotabor

5,6072 gold badges5 silver badges23 bronze badges

Comments

Michał Turczyn · Accepted Answer · 2025-07-18 05:39:50Z

0

Use IsError, because Application.Match can return an error (#N/A), which If can't handle directly:

Dim result As Variant
Dim MyArray

MyArray = Array("fee", "fi", "fo", "fum")
result = Application.Match("foo", MyArray, 0)

If Not IsError(result) Then
    Debug.Print "Pass"
Else
    Debug.Print "Not Found"
End If

answered Jul 18 at 5:39

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

Collectives™ on Stack Overflow

Type mismatch error when using match on an array in Excel VBA

3 Answers 3

`Application.Match`

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Application.Match

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

`Application.Match`