How to assign an array to a range in Excel macro?

[yunlai yang]

Hi,

I tried to assign an array ro a range in Excel macro, but it does not work.

Here is the example

sub fill_range()
dim myArray(1 to 5) as single
dim i as integer
for i=1 to 5
myArray(i)=i
next

sheets("sheet1").activate
range("a1:a5")=myArray
end sub

When the macro is run, the values of cells a1 to a5 are 1 instead of 1 to
5. That is all the cells in the range took the value of myArray(1) instead
correspondent (right) ones.

Any suggestions on solving this problem?

Thanks

My e-mail address:
(e-mail address removed). or
(e-mail address removed)

 

[Dick Kusleika]

Yunlai Yang

An single dimension array is like a row of data and you are putting it into
a column. You need to use the Transpose function to change your array to a
two-dimensional column array.

Range("A1:A5").Value = Transpose(MyArray)

 

[yunlai yang]

Hi, John

The line was not correct,as you said. Because the format of the array is
different from that of the range. I redeclared, according to suggestions
from Dick (see his advice) the array as myArray(5, 1). Now it works.

Yes, you can use loop to assign an array to a range. THis is normally OK, if
you array is not big. If it is big, it will take a long time for the reason
that there are same number of interface change between excel sheet and your
program. Using a single statement will only have one interface exchange and
same you a lot of time and frustrations.

Yunlai

 

[John Ford]

Yunlai,

I don't see a post from Dick. What was his advice?

--
jcf

<yunlai yang> wrote in message | Hi, John
|
| The line was not correct,as you said. Because the format of the array is
| different from that of the range. I redeclared, according to suggestions
| from Dick (see his advice) the array as myArray(5, 1). Now it works.
|
| Yes, you can use loop to assign an array to a range. THis is normally OK, if
| you array is not big. If it is big, it will take a long time for the reason
| that there are same number of interface change between excel sheet and your
| program. Using a single statement will only have one interface exchange and
| same you a lot of time and frustrations.
|
| Yunlai
|
|
| | >
| > Yunlai,
| > The line "range("a1:a5")=myArray" is incorrect. Since you do not tell it
| > which element(s) of myArray go to the range, the default is the first
| > element, which is "1". I don't know if you can assign the whole array at
| > once (I've never tried it), but I would use a loop in Excel to load each
| > element of myArray into the range.
| >
| > John
|
|

 

[John Ford]

Never mind... I saw Dick's response on ms.public.office.developer.vba. I didn't notice the cross-post!

| Yunlai,
|
| I don't see a post from Dick. What was his advice?
|
| --
| jcf
|
| <yunlai yang> wrote in message | | Hi, John
| |
| | The line was not correct,as you said. Because the format of the array is
| | different from that of the range. I redeclared, according to suggestions
| | from Dick (see his advice) the array as myArray(5, 1). Now it works.
| |
| | Yes, you can use loop to assign an array to a range. THis is normally OK, if
| | you array is not big. If it is big, it will take a long time for the reason
| | that there are same number of interface change between excel sheet and your
| | program. Using a single statement will only have one interface exchange and
| | same you a lot of time and frustrations.
| |
| | Yunlai
| |
| |
| | | | >
| | > Yunlai,
| | > The line "range("a1:a5")=myArray" is incorrect. Since you do not tell it
| | > which element(s) of myArray go to the range, the default is the first
| | > element, which is "1". I don't know if you can assign the whole array at
| | > once (I've never tried it), but I would use a loop in Excel to load each
| | > element of myArray into the range.
| | >
| | > John
| |
| |