I am stuck. I am trying to copy some selected rows from one column of a 2-D array to a particular column of another 2-D Array. I have tried all my knowledge, but to no avail.

Sub TestIndxPst()
    Dim varArray As Variant
    
    ReDim varArray(1 To 4, 1 To 3)
    Dim vaOut As Variant
    ReDim vaOut(1 To 4, 1 To 3)
    RowNumArray = Evaluate("transpose(Row(1" & ":" & 5 & "))")
    varArray = ThisWorkbook.Worksheets("Sheet1").Range("G2:I7")
    Application.Index([G9:I12], , 2) = Application.Transpose(Application.Index(varArray, Array(2, 3, 5, 6), 2))
    vaOut = Application.Transpose(Application.Index(varArray, Array(2, 3, 5, 6), 2))
    Application.Index(vaOut, , 2) = Application.Transpose(Application.Index(varArray, Array(2, 3, 5, 6), 2))
    For i = LBound(vaOut) To UBound(vaOut)
        Debug.Print vaOut(i, 1)
    Next i
End Sub

You can see that I can do this while writing to an excel range using Application.Index([G9:I12], , 2) = Application.Transpose(Application.Index(varArray, Array(2, 3, 5, 6), 2))

I can also do that when I output it to the first column using vaOut = Application.Transpose(Application.Index(varArray, Array(2, 3, 5, 6), 2))

But as soon as I am trying to put the result in column 2 of out array here, Application.Index(vaOut, , 2) = Application.Transpose(Application.Index(varArray, Array(2, 3, 5, 6), 2)). I get application-defined or object defined error

Any help is highly appreciated.

CodePudding user response：

Application.Index(vaOut, , 2) = .. - Any help is highly appreciated

• 1. ReDim varOut & ReDim vaOut get overruled by any eventual datafield assignment.
• 2. If you want to stick to the Index function by all means writing the rearranged values to a target range, I'd prefer an intermediate assignment something like

    Dim newArr
    newArr =  Application.Transpose(Application.Index(varArray, Array(2, 3, 5, 6), 2))

followed by

    Sheet1.Range("H9").Resize(Ubound(newArr),ubound(newArr,2)) = newArr

• 3. Application.Index([G9:I12], , 2) = .. builds no virtual array, it describes just the receiving target range which simply is H9:H12 then.

Main issue

If, however you want to enter the newArr values to the same or another array column, this is simply not possible in one go by a construction like
Application.Index(vaOut, , 2) = .. (though ressembling to your previous target range code) - you'll have to loop (e.g. through newArr and enter values to another predefined array) as @TimWilliams suggested.