Setting an Excel range equal to an array 1

[westma]

Hello

I currently have an one-dimensional array (distinctCusts) filled with strings. I am printing the values of the array onto a worksheet by looping through a "for" loop as follows:

With Range("X6")
  For iCust = 1 To nDistinctCusts
    .Offset(iCust, 0) = distinctCusts(iCust)
  Next
End With

My problem is that the array is quite large, so looping takes an undesirable amount of time. I would like to do this without looping by setting the range equal to the array - something like:

With Range("X6")
  Range(.Offset(1,0), .Offset(nDistinctCusts,0)).Value = distinctCusts
End With

I have had no success with this. Is there another way?

Thanks for your help!

 

[PHV]

I think you must use a two dimensional array.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[westma]

Could you elaborate on this a bit more? I don't understand how that is supposed to work. Thanks again!

 

[PHV]

A starting point:
Dim distinctCusts()
' code that calculates nDistinctCusts
ReDim distinctCusts(nDistinctCusts, 1)
' code that populates distinctCusts(?, 1)
With Range("X6")
Range(.Offset(1, 0), .Offset(nDistinctCusts, 0)).Value = distinctCusts
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TonyJollans]

It should work with a one dimensional array. What results or error are you getting?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[westma]

Sorry for the delayed response!

Tony:
Not getting an error - with the 1-d array, the range=array code displays only the first value in the array in all cells.

I did get the 2-d array to work as hoped, so thank you PHV, and (the rest) for you thoughts on this.