CODEWITHSUNDEEP
arraysvbafunctionexceltype-mismatch
Kerry
Kerry

Reputation: 121

VBA Application.Index with array causes type mismatch error 13

My macro throws a type mismatch error when I use 

Myarr= Application.Index(arr,0,1)

I have tried adding option explicit and defining the variables as variants but nothing seems to work.

The arr array is created from a CSV file and that contains 100000 rows and 11 columns.
The arr looks fine when I check it in the watch window (I can see the values for each row and column)

Here is the code:

Sub ArrTest()
    Dim Myarr 
    Dim Arr 
    Dim wb As Workbook 
    Set wb = Workbooks.Open("F:\People.csv")
    Arr = wb.Sheets(1).Range("A1").CurrentRegion.Value
    Myarr = Application.Index(Arr, 0, 2)
End Sub

Can anyone suggest what I am doing wrong?

Upvotes: 0

Views: 4124

Answers (1)

Tim Williams
Tim Williams

Reputation: 166531

Many of the worksheet functions have a limit of just over 65k or so when it comes to the upper bound of input arrays, so you may be hitting that. Works for me with 65k, fails with 66k rows.

Sub ArrTest()
    Dim Myarr
    Dim Arr

    Arr = Range("a1:C65000").Value
    Myarr = Application.Index(Arr, 0, 1) '<<< OK

    Arr = Range("a1:C66000").Value
    Myarr = Application.Index(Arr, 0, 1) '<<<fails

End Sub

If you want to be able to handle more than 65k upper bound, then you will need to use a loop to populate your array "slice"

Upvotes: 2

Related Questions