Did you know that there is a limit to the array that can be passed into WorksheetFunction.Index? I read a really big range into an array and I wanted to split out a row. I used the INDEX(array, rownum, 0) feature, but I kept getting a Type Mismatch error. I couldn’t figure out what the heck was going on. I ended up testing my code on a smaller range and it worked as expected.
Dim vaValues As Variant
Dim i As Long
Dim vaNew As Variant
vaValues = Sheet1.UsedRange.Resize(2 ^ 16 + 1).Value
For i = LBound(vaValues, 1) To UBound(vaValues, 1)
vaNew = Application.WorksheetFunction.Index(vaValues, i, 0)
A little experimenting and I discovered that if my range is 65,537 rows long, I can’t pass it into Index. By the way, there does not appear to be any such limitation when the function is called from a worksheet cell, e.g.
You may have noticed that the limit is the same number of rows in a 97-2003 file format. I wonder if that’s related. Am I missing something? Am I doing something wrong?