IsInArray function error: Type Mismatch VBA

Question

When I run this, it gives me an error saying

ByRef argument type mismatch

in the if statement.

I had all of the arrays declared as variants, but that also gave me an error when I tried to run the function.

Any help would be greatly appreciated.

sub sort()

Dim rowNo As Integer
Dim colNo As Integer
Dim PIList(6) As Long
Dim GSList(2) As Long
Dim FilterList(91) As Long
Dim currCellCont As Long

rowNo = 2
colNo = 2
PIList(6) = Array(0,1,2,3,4,5)
GSList(2) = Array(6,7)
FilterList(91) = Array(89752, 89753, 89754, 89755, 89756, 89757, 89758, 89759, 89760, 89761, 89762, 89763, 89764, 89765, 89766, 89767, 89768, 89769, 89770, 89771, 89772, 89773, 89774, 89775, 89776, 89777, 89778, 89779, 89780, 89781, 89782, 89783, 89784, 89785, 89786, 89787, 89788, 89789, 89790, 89791, 89792, 89793, 89794, 89795, 89796, 89797, 89798, 89799, 89800, 89801, 89802, 89803, 89804, 89805, 89806, 89807, 89808, 89809, 89810, 89811, 89812, 89813, 89814, 89815, 89816, 89817, 89818, 89819, 89820, 89821, 89822, 89823, 89824, 89825, 89826, 89827, 89828, 89829, 89830, 89831, 89832, 89833, 89834, 89835, 89836, 89837, 89838, 89839, 89840, 89841, 89842)
currCellCont = Cells(rowNo, colNo).Value

Do Until IsEmpty(currCellCont)
    If IsInArray(currCellCont, PIList) Then
        Cells(rowNo, 9).Value = "PI"
    ElseIf IsInArray(currCellCont, GSList) Then
        Cells(rowNo, 9).Value = "GS"
    ElseIf IsInArray(currCellCont, FilterList) Then
        Cells(roNo, colNo).Value = "Filter"
    End If
    currCellCont = Cells(rowNo + 1, colNo)
Loop

End Sub


Function IsInArray(lookingFor As Long, arr As Long) As Boolean
    IsInArray = Not IsError(Application.Match(lookingFor, arr, 0))
End Function

That code has more problems than just the missing () on arr. PIList(6) = Array(0,1,2,3,4,5) is trying to assign a Variant array to the 6th position (7th position if it is zero-based) of a Long array - which will give a "Type mismatch" error. — YowE3K

Scott Craner Scott Craner · Accepted Answer · 2017-12-08T19:07:43

Use variant arrays:

Sub sort()

Dim rowNo As Integer
Dim colNo As Integer
Dim PIList() As Variant
Dim GSList() As Variant
Dim FilterList() As Variant
Dim currCellCont As Long

rowNo = 2
colNo = 2
PIList = Array(0, 1, 2, 3, 4, 5)
GSList = Array(6, 7)
FilterList = Array(89752, 89753, 89754, 89755, 89756, 89757, 89758, 89759, 89760, 89761, 89762, 89763, 89764, 89765, 89766, 89767, 89768, 89769, 89770, 89771, 89772, 89773, 89774, 89775, 89776, 89777, 89778, 89779, 89780, 89781, 89782, 89783, 89784, 89785, 89786, 89787, 89788, 89789, 89790, 89791, 89792, 89793, 89794, 89795, 89796, 89797, 89798, 89799, 89800, 89801, 89802, 89803, 89804, 89805, 89806, 89807, 89808, 89809, 89810, 89811, 89812, 89813, 89814, 89815, 89816, 89817, 89818, 89819, 89820, 89821, 89822, 89823, 89824, 89825, 89826, 89827, 89828, 89829, 89830, 89831, 89832, 89833, 89834, 89835, 89836, 89837, 89838, 89839, 89840, 89841, 89842)
currCellCont = ActiveSheet.Cells(rowNo, colNo).Value
With Worksheets("Sheet1") 'Change to your sheet if always the same or ActiveSheet if on various sheets.
    Do Until currCellCont = 0
        If IsInArray(currCellCont, PIList) Then
            .Cells(rowNo, 9).Value = "PI"
        ElseIf IsInArray(currCellCont, GSList) Then
            .Cells(rowNo, 9).Value = "GS"
        ElseIf IsInArray(currCellCont, FilterList) Then
            .Cells(roNo, colNo).Value = "Filter"
        End If
        rowNo = rowNo + 1
        currCellCont = .Cells(rowNo, colNo).Value
    Loop
End With


End Sub


Function IsInArray(lookingFor As Long, arr() As Variant) As Boolean
    IsInArray = Not IsError(Application.Match(lookingFor, arr, 0))
End Function

IsInArray function error: Type Mismatch VBA

1 Answers