CODEWITHSUNDEEP
arraysexcelvba
Diego
Diego

Reputation: 13

What is causing array type mismatch?

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

Upvotes: 0

Views: 1232

Answers (1)

John Coleman
John Coleman

Reputation: 51998

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

Upvotes: 0

Related Questions