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 Removed). or
(E-Mail Removed)

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" <(E-Mail Removed)> wrote in message
news:mjensen-(E-Mail Removed)...
>
> 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

Yunlai,

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

--
jcf

<yunlai yang> wrote in message news:bf1lb7$ggt$(E-Mail Removed)...
| 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" <(E-Mail Removed)> wrote in message
| news:mjensen-(E-Mail Removed)...
| >
| > 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
|
|

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

"John Ford" <(E-Mail Removed)> wrote in message news:#(E-Mail Removed)...
| Yunlai,
|
| I don't see a post from Dick. What was his advice?
|
| --
| jcf
|
| <yunlai yang> wrote in message news:bf1lb7$ggt$(E-Mail Removed)...
| | 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" <(E-Mail Removed)> wrote in message
| | news:mjensen-(E-Mail Removed)...
| | >
| | > 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
| |
| |

[bbcalagu]

This will work

Sub test()
Dim strA(5) As String

strA(0) = "A"
strA(1) = "B"
strA(2) = "C"
strA(3) = "D"
strA(4) = "E"

Cells(1, 1).Offset(0, 0).Resize(1, UBound(strA)) = strA
End Sub

I remember seen this solution in a book long back; thought of posting this if in case helps to others

[Dzasta]

You're original post was almost correct.

to use one line to dump an array to a range or vice versa, make sure to use the .value property of the range.

For example:

Use myArray=Range("A1:Z26").Value
Not myArray=Range("A1:Z26")

The same goes for the reverse
Correct
Range("A1:B12").Value=myArray

Incorrect:
Range("A1:B12")=myArray

[apka]

Copying an Array to a Worksheet Range without looping:

From what I read in the post, yes Dick's solution worked.

Array declaration must be atleast 2-Dim - indicates (Row, Col) ??
If you intend to copy to a multiple column range, you'll need to increment the 2nd dimension.

These work:

Option Base 1
'Single Column Range
Sub Test1()
Dim myArray(5, 1) As Double
Dim i As Integer
For i = 1 To 5
myArray(i, 1) = i / 3
Next
Sheets("sheet1").Activate
Range("a1:a5") = myArray
End Sub


'Multiple Column range
Sub Test2()
Dim myArray(5, 2) As Double
Dim i As Integer
For i = 1 To 5
myArray(i, 1) = i / 3
myArray(i, 2) = i / 10
Next
Sheets("sheet1").Activate
Range("a1:b5") = myArray
End Sub