如何填充动态2D阵列？

Why does this:

为什么这样:

Dim Arr As Variant
p = 1
For i = 1 To LRow
If Sheets("Data").Range("U" & 4 + i).Value > 0 Then
    ReDim Preserve Arr(1 To p, 1 To 2)
    Arr(p, 1) = Sheets("Data").Range("U" & 4 + i).Value
    Arr(p, 2) = Sheets("Data").Range("N" & 4 + i).Value
    p = p + 1
End If
Next

results in "run time error 9 - Subscript out of range" at the ReDim line? I do not know the number of array rows prior to entering the for loop. The column number should always be 2. Doing the same thing but with an 1D Array works, though! Any help?

在ReDim线上导致“运行时错误9 - 下标超出范围”?我不知道进入for循环之前的数组行数。列号应始终为2.尽管如此,使用1D数组也可以做同样的事情!有帮助吗?

2 个解决方案

#1

If you use ReDim Preserve you can only resize the last dimension of an array. See here:https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/redim-statement
If you are looking for a solution, then you can swap array to be Arr(2,p) as you say column number will always be 2.

如果使用ReDim Preserve,则只能调整阵列的最后一个维度。请参阅此处:https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/redim-statement如果您正在寻找解决方案,那么您可以将数组交换为Arr(2 ,p)如你所说,列号将始终为2。

#2

As stated, you can only redim preserve the last dimension.

如上所述,您只能重新保留最后一个维度。

But you can also use other methods to find the number of "rows" needed and set that prior to rediming the array:

但是您也可以使用其他方法来查找所需的“行”数,并在重新引导数组之前设置它们:

Dim Arr As Variant
p = 1
dim rws as long
rws = Application.WorkSheetFunction.CountIf(Sheets("Data").Range("U5:U" & Lrow+4),">0")
Redim Arr(1 to rws,1 to 2)
For i = 1 To LRow
If Sheets("Data").Range("U" & 4 + i).Value > 0 Then

    Arr(p, 1) = Sheets("Data").Range("U" & 4 + i).Value
    Arr(p, 2) = Sheets("Data").Range("N" & 4 + i).Value
    p = p + 1
End If
Next

#1

#2