CODEWITHSUNDEEP
arraysexcelvba
Matt Carter
Matt Carter

Reputation: 21

VBA application.index Array() in rows doesn't work

I am trying to use the Application.Index function in excel VBA.

I currently have an array(4,11139) that I am trying to splice into two separate arrays one containing the first 2 rows (2,11139) and the other containing the 3rd/4th rows (2, 11139). Various searches have suggested this code is the correct method but it's not working properly: (I've condensed the code to what is relevant)

Dim Adjusted_Data_Array() As Single
Dim Final_Data_Array() As Variant
Dim Sys1_Data_Array() As Variant
Dim Sys2_Data_Array() As Variant
'Function that creates a 4,11139 variant array
Final_Data_Array = Strip_Erroneous(Adjusted_Data_Array, 1)
'Splice first two rows
Sys1_Data_Array = Application.Index(Final_Data_Array, Array(1, 2), 0)
'splice 3/4th rows
Sys2_Data_Array = Application.Index(Final_Data_Array, Array(3, 4), 0)

So I should now have 2 arrays each containing half the data from the first array. But all I get in the new arrays is a one dimensional array with two rows. Like this:

Screenshot of the watches from the 3 arrays discussed above.

Screenshot of the watches from the 3 arrays discussed above.

I've tried it with the Array(1,2) in the columns and that doesn't work either.

I can solve the issue in this regard by simply splitting into 4 separate one dimensional arrays (that works fine, it's the Array(1,2) or Array(3,4) that doesn't seem to. But I know in a future work coming up I'll be needing to splice much bigger arrays so would like to understand why it's not working ready for that. Big thanks in advance.

Edit: Tried using:

Sys1_Data_Array = Application.Index(Final_Data_Array, Application.Transpose(Array(1, 2)), 0)

as per charles suggestion below but end up with the array's simply switched indices as shown:

Screenshot of watches from transpose(array(1,2) attempt

Screenshot of watches from transpose(array(1,2) attempt

Upvotes: 2

Views: 5916

Answers (2)

Docmarti
Docmarti

Reputation: 386

TheAddress = "row(1:2)"
TheRows = Application.Evaluate(TheAddress) 'must be a 2 dimensions array 
TheColumns = Array(1, 2, 3)'must be a 1 dimension array 
Sys1_Data_Array = Application.Index(Final_Data_Array, TheRows, TheColumns) 
Cells(9, 1).Resize(UBound(Sys1_Data_Array), UBound(Sys1_Data_Array, 2)) = Sys1_Data_Array

Upvotes: 1

Charles Williams
Charles Williams

Reputation: 23540

You can use the INDEX approach if you wrap the ARRAY inside a TRANSPOSE

var1 = Application.Index(varData, Application.Transpose(Array(1, 2)), 0)
var2 = Application.Index(varData, Application.Transpose(Array(3, 4)), 0)


Apologies: actually this does not work!

But its about 2.5 times slower than just copying the relevant parts of the array using VBA.

For k = 1 To UBound(varData, 2)
    For j = 1 To 2
        var1(j, k) = varData(j, k)
        var2(j, k) = varData(j + 2, k)
    Next 
Next

Upvotes: 1

Related Questions