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

At what line does the error occur and what is the error message? — Andy G
– Andy G, Commented Aug 1, 2013 at 17:36
The error occurs here: CategoryData = Selection.Value Something along the lines of cannot assign array. — user2643068
– user2643068, Commented Aug 2, 2013 at 10:04

Community · Accepted Answer · 2018-05-08 21:48:18Z

8

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.

edited May 8, 2018 at 21:48

CommunityBot

11 silver badge

answered Aug 1, 2013 at 17:42

Andy G

19.4k5 gold badges49 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2643068 Over a year ago

Two things: First, I have tried using variant and I still get the same error. Second, my array size exactly matches the data set. Though I can use a for loop to assign values to the array, this takes far too long and it would be much easier to copy the whole range into the array.

Andy G Over a year ago

Create a small separate procedure to test. Try stepping through your code. Set a Breakpoint by pressing F9. It is likely because this assignment can only be performed once and you have it in a loop. IMO you'll have to split it into two procedures, with the second procedure able to re-define (re-declare) CategoryData each time.

71GA Over a year ago

In this case array CategoryData will be converted to a single String if selection is only a single cell. Right?

Robin Dirksen · Accepted Answer · 2017-01-19 13:44:54Z

1

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

edited Jan 19, 2017 at 13:44

Robin Dirksen

3,42228 silver badges41 bronze badges

answered Jan 19, 2017 at 12:58

user7441130

111 bronze badge

1 Comment

71GA Over a year ago

In this case array NewArray will be converted to a single String if selection is only a single cell.

Collectives™ on Stack Overflow

How to copy selected range into given array?

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related