vba 如何将选定范围复制到给定数组中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18000617/
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
How to copy selected range into given array?
提问by user2643068
I have a repeating list of 145 categories, with 15 columns of data for each category. I am consolidating this list by reducing the number of categories to 24 and adding the corresponding data.
我有一个包含 145 个类别的重复列表，每个类别有 15 列数据。我通过将类别数量减少到 24 并添加相应数据来合并此列表。
For example, If initially I had Categories A B C D E F G and I consolidated, I would add all the values in A and, say F, to get a new category.
例如，如果最初我有类别 ABCDEFG 并且我合并了，我会添加 A 中的所有值，比如 F，以获得一个新类别。
Another issue is that all these 145 categories are repeated over 60 time periods. So I have to consolidate the data separately for each time period.
另一个问题是所有这 145 个类别都在 60 个时间段内重复。所以我必须单独合并每个时间段的数据。
To do this, I am trying to use arrays.
为此，我正在尝试使用数组。
Sub CategoriesToSectors()
Dim k As Integer
Dim j As Integer
Dim p As Integer
Dim Destination As Range
' p is just a filler/dummy variable until I later decide which categories go into which sector
Dim CategoryData(144, 14) As Long
Dim SectorData(23, 14) As Long
k = 0
' k should go Upto 60
' I first copy the data from a range in the first worksheet into the array CategoryData
' Then I move 145 rows down for the next time-period's data and repeat this whole process
While k < 60
Sheets("ReformattedData").Select
Range("B1:P145").Select
ActiveCell.CurrentRegion.Offset(k * 145, 0).Select
CategoryData = Selection.Value
For j = 0 To 14
SectorData(0, j) = CategoryData(1, j) + CategoryData(6, j) + CategoryData(8, j) + CategoryData(13, j)
For p = 1 To 23
SectorData(p, j) = CategoryData(15, j) + CategoryData(19, j) + CategoryData(31, j) + CategoryData(44, j)
Next p
Next j
' paste consolidated sectordata array one below another in SectorData worksheet
Sheets("SectorData").Select
Range("B2").Select
Set Destination = ActiveCell.Offset(k * 25, 0)
Destination.Resize(UBound(SectorData, 1), UBound(SectorData, 2)).Value = SectorData
Wend
End Sub
As you can see, what I am doing is first trying to copy the first range block into the CategoryData array. Then, I am combining the data into the sector array - I have just used repeated values to test it - the for loop with p should not exist. I will eventually use 24 different statements to create the SectorData Array.
如您所见，我首先尝试将第一个范围块复制到 CategoryData 数组中。然后，我将数据组合到扇区数组中——我刚刚使用了重复的值来测试它——带有 p 的 for 循环不应该存在。我最终将使用 24 种不同的语句来创建 SectorData 数组。
Then I paste the Consolidated data onto another sheet. I Go back to the first sheet and move my selection down for the next range block (145 cells below the first cell) then I select this data and repeat.
然后我将合并的数据粘贴到另一个工作表上。我返回到第一张工作表并向下移动我的选择以进行下一个范围块（第一个单元格下方的 145 个单元格），然后我选择此数据并重复。
This does not seem to work - error in entering data into the first array - CategoryData.
这似乎不起作用 - 将数据输入到第一个数组 - CategoryData 时出错。
Help would be appreciated.
帮助将不胜感激。
Thank you
谢谢
回答by Andy G
In order to copy a Range into a VBA array you have to use a Variant:
为了将 Range 复制到 VBA 数组中，您必须使用 Variant：
Dim CategoryData() As Variant
'or just CategoryData As Variant (no brackets)
'then the following will work
CategoryData = Selection.Value
Once you've transferred the data you can examine UBoundfor CategoryData.
传输数据后，您可以检查UBoundCategoryData。
There is a useful discussion here at cpearson.
在 cpearson有一个有用的讨论。
You can set a Range to an array (SectorData in your example) without it being a Variant, as long as the dimensions are the same.
只要维度相同，您就可以将 Range 设置为数组（在您的示例中为 SectorData）而不是 Variant。
回答by user7441130
Try this:
尝试这个：
Sub RangeToArray()
Dim NewArray As Variant
Dim SourceRange As Range
Set SourceRange = Selection.CurrentRegion
NewArray = SourceRange.Value
Stop 'to check the result in Immediate Window
End Sub
