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

  • Thread starter Thread starter yunlai yang
  • Start date Start date
Y

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)
 
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,

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
|
|
 
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
| |
| |
 
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
 
Last edited:
Almost There

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
 
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
 
Back
Top