如何复制选定的范围为特定的阵列? [英] How to copy selected range into given array?
问题描述
我有145类别重复的列表,每个类别的数据,15列。
我通过减少类别数至24并加入相应的数据合并此列表。
例如,
如果一开始我有类别A B C D E F G和我巩固,我想补充的所有值A和,说楼得到一个新的类别。
的另一个问题是,所有这些145类别重复60的时间段。所以,我必须单独整合数据为每个时间段。
要做到这一点,我试图使用数组。
子CategoriesToSectors()
昏暗的K作为整数
昏暗Ĵ作为整数
昏暗数p作为整数
昏暗的目的地范围
'P只是一个填料/虚拟变量,直到后来我才决定哪些类别进入该领域
昏暗CategoryData(144,14)只要
昏暗SectorData(23,14)只要K = 0
数k应该去高达60
我第一次将数据从一个区域复制的第一个工作表到数组CategoryData
接我一招145向下行的下一个时间段的数据,重复整个过程
虽然K< 60
表(ReformattedData)。选择
范围(B1:P145)选择。
ActiveCell.CurrentRegion.Offset(K * 145,0)。选择
CategoryData = Selection.Value对于j = 0到14
SectorData(0,j)的= CategoryData(1,J)+ CategoryData(6,j)的+ CategoryData(8,j)的+ CategoryData(13,j)的
对于p = 1〜23
SectorData(P,j)条= CategoryData(15,j)的+ CategoryData(19,j)的+ CategoryData(31,j)的+ CategoryData(44,j)的
下一个p
下面j
粘贴下面另一个SectorData工作综合sectordata阵列中的一个
表(SectorData)。选择
范围(B2)。选择
设置目标= ActiveCell.Offset(K * 25,0)
Destination.Resize(UBound函数(SectorData,1),UBound函数(SectorData,2))。值= SectorData
WEND
结束小组
正如你所看到的,我在做什么首先尝试第一个范围块复制到CategoryData阵列。然后,我将数据合并到该部门阵列 - 我刚使用重复的值来测试它 - 为与对循环不应该存在。我最终将使用24种不同的语句来创建SectorData阵列。
然后我合并的数据粘贴到其他工作表。我回到第一张上下移动我的选择下一个范围块(145细胞中的第一个单元格下方),那么我选择这个数据并重复。
这似乎并不工作 - 错误数据输入到第一个数组 - CategoryData P>
帮助将AP preciated。
感谢您
为了一个范围复制到你必须使用一个Variant一个数组VBA:
暗淡CategoryData()为Variant
或只是CategoryData为Variant(无支架)
那么下面的工作
CategoryData = Selection.Value
一旦你转,你可以检查数据 UBound函数
为CategoryData。
有一个有用的讨论在这里在cpearson 。
您可以将范围设置为一个数组(SectorData在你的例子),而它是一个变量,只要尺寸是相同的。
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.
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.
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.
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.
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.
This does not seem to work - error in entering data into the first array - CategoryData.
Help would be appreciated.
Thank you
In order to copy a Range into a VBA array you have to use a 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 UBound
for CategoryData.
There is a useful discussion here at 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.
这篇关于如何复制选定的范围为特定的阵列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!