2范围内的维数组 [英] 2 Dimensional array from range
问题描述
我在单元格 B6:H14
中的Excel工作表中有文本数据。
I have text data in Excel worksheet in the cells B6:H14
.
有些行将包含2个单元格,而其他行将有4个单元格,另外一些将具有7.如何将它们复制到二维数组?我知道尺寸已经和所以,我的尺寸没有被声明为动态代码。
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.
我需要使用一个循环(我正在计划使用)?
Do I need to use a loop (which I am currently planning to use)?
还是有更简单/更优雅的方式?
Or is there an easier / more elegant way?
推荐答案
假设您的电子表格看起来像这样
Assuming your spreadsheet looks kind of like this
有一个非常简单的方法来坚持2D数组
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
这篇关于2范围内的维数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!