How to copy VBA array into range of cells?
 

What is the syntax for copying a VBA array into a range of cells?

I know I can copy into each cell individually in a for-loop. I am
wondering if there is a way to do it en masse in a single statement.

For example:

dim xdata(10) as variant
for i = 1 to 10: xdata(i-1) = i: next i
range("a1:a10") = xdata

That does not work the way that I want. It copies xdata(0) into each
of A1:A10 instead of copying xdata(1) into A2, xdata(2) into A3, etc.
I want the latter.

How to copy VBA array into range of cells?
 

Sub justdoit()
s = Array(1, 2, 3)
Range("A1:C1") = s
End Sub

in a column, you should transpose
--
Gary''s Student - gsnu2007b

How to copy VBA array into range of cells?
 

How to copy VBA array into range of cells?
 

On Dec 7, 11:38 am, Alan Beban wrote:
In any event, you're trying to copy a horizontal array into a vertical
range. The following will copy the first 10 elements of the array into
a1:a10:
range("a1:a10") = Application.Transpose(xdata)

Thanks. Based on that, I discovered that the following works fine:

dim xdata(10,0) as variant
[...assign values to xdata(i,0)...]
range("a1:a10") = xdata

I am surprised that if xdata(10) specifies a row spanning 10 columns,
xdata(10,0), not xdata(0,10), specifies a column spanning 10 rows. Oh
well.

I also learned that the following is one way (best? only?) to copy a
range into a VBA array:

dim xdata as variant
xdata = range("a1:a10")

But that is equivalent to declaring xdata(1 to 10, 1 to 1).

(I finally found the information in my reference book.)

Thanks again.

How to copy VBA array into range of cells?