Type Mismatch error with Application.Match in VBA Macro

Question

I am trying to use Application.Match to delete a series of lines from a spreadsheet before using the remaining lines to edit an existing spreadsheet. Here is the code that I am using, which gives me a Type Mismatch error. I have used similar logic with smaller datasets previously and not had a problem:

If IsError(Application.Match(Fund_Inv_Cusip_Change(LineNumber), Fund_Inv_Cusips_to_Edit, 0)) Then
        If DeletionRange Is Nothing Then
            Set DeletionRange = Cells(Override_Workbook_Line + 3, 1).EntireRow
        Else
            Set DeletionRange = Union(DeletionRange, Cells(Override_Workbook_Line + 3, 1).EntireRow)
        End If
    End If
Next LineNumber
DeletionRange.Delete Shift:=xlUp
Set DeletionRange = Nothing

Fund_Inv_Cusips_to_Edit is declared an array with 66,124 entries, declared as a string. FUnd_Inv_Cusip_Change is an array with 3329 entries, declared as a string. Using print(typename(fund_inv_cusips_to_edit()) in the Immediate window returns String() for each array, so I'm not certain why this is a type mismatch.

Any help would be much appreciated. I have read through a lot of posts on this board and others and not seen something that directly addresses this issue. Thank you!

Dick Kusleika Dick Kusleika · Accepted Answer · 2014-09-08T21:05:09

Application.Match can only take 65,536 element arrays in the second argument. In the Immediate Window

?application.match(1,range("A1:A65536").Value,false)

no error

?application.match(1,range("A1:A65537").Value,false)

type mismatch error.

You could loop. It's not very efficient, but it might be quicker than you think. Or you could put it in a disconnected recordset, but it might take longer to load that up than just looping.

Type Mismatch error with Application.Match in VBA Macro

1 Answers