我正在嘗試從不連續的行中獲取資料。列是固定的，但行號不同。我期待的結果是一個二維陣列。我不知道我做錯了什么，但是使用索引函式的切片不起作用。
'Just for example to get the data from row number 100, 500 and 900 and the columns from A to F
arr = Application.Index(Sheet2.Range("A:F"), array(100, 500, 900))
我想如果第三個引數留空，行切片就會完成。但結果陣列是一維的（大小為 3）并填充了錯誤 2023。
所以我也通過給出第三個引數再次嘗試。
arr = Application.Index(Sheet2.Range("A:F"), array(100, 500, 900), array(1, 2, 3, 4, 5, 6))
即使現在結果陣列是一維的（大小為 6），但前三個索引有資料，其余的有錯誤 2042。是否有可能通過對二維陣列進行切片來獲得二維陣列？如果是，請指出我正確的方向。
uj5u.com熱心網友回復：
行陣列需要垂直：
arr = Application.Index(Sheet2.Range("A:F"), Application.Transpose(Array(100, 500, 900)), Array(1, 2, 3, 4, 5, 6))
人們總是可以創建一個從 1 到列數的一維數字陣列，并使用它：
Dim rng As Range
Set rng = Sheet2.Range("A:F")
Dim test As Variant
ReDim test(rng.Columns.Count - 1)
Dim i As Long
For i = LBound(test) To UBound(test)
test(i) = i 1
Next i
Dim arr As Variant
arr = Application.Index(rng, Application.Transpose(Array(100, 500, 900)), test)
uj5u.com熱心網友回復：
切片陣列行
請注意，對于連續列，您可以使用：
[Row(1:1)] ' first column [Row(1:3)] ' first three columns [Row(2:5)] ' four columns after the first [Row(3:4)] ' two columns after the second如果您還需要選擇非連續列的靈活性，那么
[Row(1:6)]您可以使用：Application.Transpose([{1,3,4,6}]) Application.Transpose(Array(1, 3, 4, 6))
快速修復
- 從基于一的二維陣列中的二維基于一的陣列回傳三行乘六列。
Sub QuickFix()
' Don't use entire columns, it takes too long.
Dim rg As Range: Set rg = Sheet2.Range("A1").CurrentRegion.Columns("A:F")
'Debug.Print rg.Address(0, 0)
Dim arr As Variant
arr = Application.Transpose(Application.Index(rg.Value, [{100,500,900}], [Row(1:6)]))
' Three rows, six columns ('H1:M3')
Sheet2.Range("H1").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End Sub
一項研究
Sub Random()
Dim arr() As Variant
' A Row (1D one-based: one row, three columns)
arr = [{3,5,7}]
Debug.Print "Row (Random)"
Debug.Print LBound(arr), UBound(arr)
' A Column (2D one-based: three rows, one column)
arr = Application.Transpose([{3,5,7}])
Debug.Print "Column (Random)"
Debug.Print LBound(arr, 1), UBound(arr, 1), LBound(arr, 2), UBound(arr, 2)
End Sub
Sub Sequence()
Dim arr() As Variant
' A Row (1D one-based: one row, six columns)
arr = Application.Transpose([(Row(1:6))])
Debug.Print "Column (Sequence)"
Debug.Print LBound(arr, 1), UBound(arr, 1)
' A Column (2D one-based: six rows, one column)
arr = [Row(1:6)]
Debug.Print "Row (Sequence)"
Debug.Print LBound(arr, 1), UBound(arr), LBound(arr, 2), UBound(arr, 2)
End Sub
Sub TwoD()
' Source Array (2D one-based: ten rows, six columns)
Dim sData As Variant: sData = Range("A1:F10")
Debug.Print "Source"
Debug.Print LBound(sData, 1), UBound(sData, 1), LBound(sData, 2), UBound(sData, 2)
' Transposed Array (2D one-based: six rows, three columns)
Dim tData As Variant
tData = Application.Index(sData, [{3,5,7}], [Row(1:6)])
Debug.Print "Transposed (Wrong)"
Debug.Print LBound(tData, 1), UBound(tData, 1), LBound(tData, 2), UBound(tData, 2)
' Destination Array (2D one-based: three row, six columns)
Dim dData As Variant
dData = Application.Transpose(Application.Index(sData, [{3,5,7}], [Row(1:6)]))
Debug.Print "Destination (Correct)"
Debug.Print LBound(dData, 1), UBound(dData, 1), LBound(dData, 2), UBound(dData, 2)
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/382276.html