What is causing array type mismatch?

Question

I am using a for loop to create an array containing valid order numbers that will then be used as search criteria for another query table. The next for loop searches for each order number in the previously created array and deletes the row if it is not in the array. I'd like to know why I'm getting a type mismatch error in the conditional statement line of the search function. I tried declaring the array as both a variant and as an array with individual string elements. Here is the trimmed down code, thanks in advanced!

Sub VistaArray()
Dim n As Integer, lastrow As Integer, ordern As String, vista() As Variant
'ADDING NEW ELEMENTS TO ORDER NUMBER ARRAY
For n = 2 To lastrow
    i = n - 2
    ReDim Preserve vista(i)
    ordern = Worksheets(Sheet1).Cells(n, 1).Value
    vista(i) = ordern
Next n

'REMOVING LINES FROM SECOND TABLE THAT AREN'T IN THE ARRAY
lastrow = Worksheets(Sheet2).Range("A1").End(xlDown).Row
For n = 2 To lastrow
    ordn = ActiveSheet.Cells(n, 1).Value
    If IsInArray(ordn, vista) Then
    Else
        '...REMOVE LINE FROM QUERY TABLE...
    End If
Next n
End Sub

Function IsInArray(ordn As String, vista As Variant) As Boolean
IsInArray = (UBound(Filter(ordn, vista)) > -1) '***ERROR OCCURS ON THIS LINE***
End Function

Sorry, I cut out clutter for this post and removed the line where I declared the array as variant without realizing that was key info. I've added that line into the post. — Diego
ordn is a string, not an array. Filter is expecting an array. Perhaps you need to switch the order of the two arguments to Filter. — John Coleman
Well that explains it. I had the variables flipped. 'vista' is the array. Thanks, and sorry for the waste of time. — Diego
No waste of time -- I learned something today. I've never had occasion to use that function in VBA — John Coleman
You know you can go directly from a range to an array without a loop? vista = Sheet1.Range("A2").Resize(num_rows,1).Value2, but vista needs to be defined as two dimensional array. — John Alexiou

John Coleman John Coleman · Accepted Answer · 2016-03-08T14:59:09

The function Filter expects an array for its first argument and a string for its second. You have that reversed. The following should work:

Function IsInArray(ordn As String, vista() As Variant) As Boolean
    IsInArray = (UBound(Filter(vista, ordn)) > -1)
End Function

What is causing array type mismatch?

1 Answers