vba 来自范围的二维数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18481330/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
2 Dimensional array from range
提问by Siddhartha
I have text data in Excel worksheet in the cells B6:H14.
我在 Excel 工作表中的单元格中有文本数据B6:H14。
Some rows will have 2 cells with contents while others have 4 and some will have 7. How do I copy these to a 2 dimensional array? I know the dimensions already and so, I am ok with the dimensions not being declared dynamic code.
有些行将有 2 个单元格和内容，而其他行有 4 个，有些将有 7 个。如何将这些复制到二维数组？我已经知道尺寸了，所以我对没有声明为动态代码的尺寸没问题。
Do I need to use a loop (which I am currently planning to use)?
我是否需要使用循环（我目前计划使用）？
Or is there an easier / more elegant way?
或者有更简单/更优雅的方式吗？
回答by
Assuming your spreadsheet looks kind of like this
假设您的电子表格看起来像这样
There is a really easy way to stick that in a 2D array
有一种非常简单的方法可以将其粘贴在二维数组中
Dim arr as Variant
arr = Range("B6:H14").Value
The easiest way to print this array back to spreadsheet
将此数组打印回电子表格的最简单方法
Sub PrintVariantArr()
Dim arr As Variant
arr = Range("B6:H14")
Range("B16").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub
Or you can iterate/loop the array
或者您可以迭代/循环数组
Sub RangeToArray()
Dim arr As Variant
arr = Range("B6:H14").Value
Dim r As Long, c As Long
r = 16
c = 2
Dim i, j
For i = LBound(arr, 1) To UBound(arr, 1)
For j = LBound(arr, 2) To UBound(arr, 2)
Cells(r, c) = arr(i, j)
c = c + 1
Next j
c = 2
r = r + 1
Next i
End Sub
And your array printed back to the spreadsheet
并将您的数组打印回电子表格
