Data Arrays into Ranges?????

Posted by Ron Dionne on October 23, 2001 1:13 PM

I have data stored in arrays that I'd like to paste
into a worksheet. If I try to use this format:

Set corrWS = newWB.Worksheets("Correlation")
corrWS.Range("A1:A44") = ParamSdevArray

All the cells in the range get filled with
ParamSdevArray(0). I'd hate to have to cycle thru
each array element and fill the cells individually
if I don't have to. Is there a better way?

Posted by Juan Pablo on October 23, 2001 1:24 PM

I haven't tested this, but try

corrWS.Range("A1:A44").FormulaArray = ParamSdevArray

Juan Pablo

Posted by Ron Dionne on October 23, 2001 1:30 PM

Thanks, but it yields the same result.
I've been able to substitute array names for
ranges in worksheetfunctions so I was hoping
there was an easy way to do it. Any other ideas?

Posted by Damon Ostrander on October 24, 2001 12:33 PM

Hi Ron,

The trick here is that you cannot assign an array to a range, but you CAN assign a variant CONTAINING an array to a range. So the code should look like this:

Set corrWS = newWB.Worksheets("Correlation")
Dim ExcelArray As Variant
ExcelArray = ParamSdevArray
corrWS.[A1:A44] = ExcelArray

You will find that this is very efficient--much faster than using a loop to set every cell to an array element.

Happy computing.

Damon