Quick way of copying Range to (sub)Array ?

[John Mitchell]

dim a1(1 to 2, 1 to 3)
a1=range("a1:c3")

loads values from the selected range into the 2D array. Is there an
elegant way of doing the same sort of thing with a 2D subset of a 3D array?

dim a2(1 to 2, 1 to 3, 1 to 2)
a2(,,2)=range("a1:c3")

The left hand side of the last statement is syntactically incorrect but
hopefully serves to demonstrate what I am trying to achieve. I'd like to
avoid having to use a loop to load values into the array one element at
a time.

Regards, John M

 

[John Mitchell]

Typo..

"a1:c3" should be "a1:c2". Apologies for any confusion. JM.

 

[Alan Beban]

No, it doesn't. Either of the following does:

Dim a1 [or, in xl 2000 and later, Dim a1()]
a1 = Range("a1:c2")

or

Dim a1 [or, in xl 2000 and later, Dim a1()]
Redim a1(1 to 2, 1 to 3)
a1 = Range("a1:c2")

Some time back, Harlan Grove posted a function, aresize, that I believe
would do what you are asking, called, e.g.,

Dim a1
a1=Range("a1:c2")
aresize a1, 2, 3, 2

It doesn't do it without looping, of course, but the looping is
pre-packaged.

Alan Beban