How to copy selected range into given array?

Question

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

Answer 1

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.

Answer 2

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