Possible to copy all the values in an array to cells at once from VBA?

[juschuma]

I have an array called myArray(3).

It's values are myArray("12", "5", "12", "35&quot

I want to copy each value into a cell on a spreadsheet. Usually I do something like this:

i=0
Do while i < 4
Range(&quot;A1&quot.Offset(0, i).Formula = myArray(i)
i = i+1
Loop

Is it possible to do this in one line and not use the loop?

 

[SkipVought]

Hi, juschuma,

You might want to tune up your routine to make more general...

lRowStart = 1 - LBound(MyArray, 1)
For i = LBound(MyArray, 1) to UBound(MyArray, 1)
   Cells(lRowStart + i, 1).Value = MyArray(i)
Next

There's no other way that I know of. Skip,
SkipAndMary1017@mindspring.com

 

[Nikita6003]

Hi juschuma,

You can assign the value of an array to a range, as long as that range has the same dimensions as your array

This is from a bit of my code which dumps the list I use for an Excel combobox in a worksheet:

Dim l_sResultArray() as String
Dim l_lStartRow as Long
Dim l_iStartCol as Integer

'<code to fill the array here ...>

'Dump array in range
Range(Cells(l_lStartRow, l_iStartCol), Cells(l_lStartRow, p_StartCol).Offset(UBound(l_sResultArray) + 2, <the number of columns your array has - 1>)) = l_sResultArray

Hope this helps

Cheers
Nikki