PDA

View Full Version : Defining an array as a range of cells



kualjo
07-29-2010, 03:35 PM
Is there a way, once I have declared my 2-dimensional array and subscripts, to define a range of cells as the location of the array? For example, if I have dim testarray (5, 5) and want that 5x5 range to be cells A1:E5, how can I tell VBA to use that range as the array without having to define all 25 cells one by one? I tried something like testarray = Range("A1:E5") but of course this didn't work. My actual working range is going to be significantly larger, so I'm hoping for a better solution.

Bob Phillips
07-29-2010, 03:49 PM
Are you trying to load the array from the range, because that is the correct syntax?

kualjo
07-29-2010, 05:19 PM
Yes, the intent would be to define an x*y array, then have a cell range of x*y cells automatically assign values to each variable within the array. With the syntax above, I get this error:

Compile error:
Can't assign to array

I hope that I'm just not doing it right and that there is a way to assign all the values all at once as opposed to x*y value assignment statements.

Bob Phillips
07-30-2010, 12:36 AM
It does work I can assure you.

I think I see your problem, instead of defining the array as you do, sizing it i the definition, dec lare it like this



Dim testarray As Variant

kualjo
07-30-2010, 11:31 AM
That works! I will proceed from here, and if I have any other issues, I'll post another reply.

What I can't figure out is why none of the VBA books I have address this method. Seems like one of the most obvious ways to populate an array with values without having to write miles of code.

Thanks!