Setting Range = Array of User-Defined Types
 

I have a large array ['P()'] of Public Types ['PORecord'] from which I would
like to write some of the sub-types [eg. 'PO_OutputDate'] to a spreadsheet at
the end of a set of calculations. Currently I am using a "For...Next" loop,
but this is slooww.

Currently my Code goes something like this:

'Start Code
For q = UBound(P, 1) To LBound(P, 1)
Sheet1. Range(Info.PO_Output_Col & q).Value = P(q).PO_OutputDate
Next q
'End Code

What I would like to do is write all the OutputDates in the array at once to
a range rather than stepping through it bit by bit. This is what I had in
mind...

'start code
Sheet1.Range("Z:Z") = P().PO_OutputDate
'end code

.... however I get an "invalid qualifier" error. What am I doing wrong? How
can I fix this?

Thanks in advance,
Steve

Setting Range = Array of User-Defined Types
 

Not tested but something like:

Dim i As Long
Dim nDate As Long
Dim DateArray() As Variant

nDate = UBound(P)
ReDim DateArray(nDate, 1)

For i = 1 To nDate
DateArray(i, 1) = P(i).PO_OutputDate
Next i

Range("Z1:Z" & nDate) = DateArray


"steve4446" wrote:

I have a large array ['P()'] of Public Types ['PORecord'] from which I would
like to write some of the sub-types [eg. 'PO_OutputDate'] to a spreadsheet at
the end of a set of calculations. Currently I am using a "For...Next" loop,
but this is slooww.

Currently my Code goes something like this:

'Start Code
For q = UBound(P, 1) To LBound(P, 1)
Sheet1. Range(Info.PO_Output_Col & q).Value = P(q).PO_OutputDate
Next q
'End Code

What I would like to do is write all the OutputDates in the array at once to
a range rather than stepping through it bit by bit. This is what I had in
mind...

'start code
Sheet1.Range("Z:Z") = P().PO_OutputDate
'end code

... however I get an "invalid qualifier" error. What am I doing wrong? How
can I fix this?

Thanks in advance,
Steve

Setting Range = Array of User-Defined Types
 

Oh, BTW, I use Option Base 1 always so adjust your array dimensions
accordingly if you use zero-based arrays.

"steve4446" wrote:

I have a large array ['P()'] of Public Types ['PORecord'] from which I would
like to write some of the sub-types [eg. 'PO_OutputDate'] to a spreadsheet at
the end of a set of calculations. Currently I am using a "For...Next" loop,
but this is slooww.

Currently my Code goes something like this:

'Start Code
For q = UBound(P, 1) To LBound(P, 1)
Sheet1. Range(Info.PO_Output_Col & q).Value = P(q).PO_OutputDate
Next q
'End Code

What I would like to do is write all the OutputDates in the array at once to
a range rather than stepping through it bit by bit. This is what I had in
mind...

'start code
Sheet1.Range("Z:Z") = P().PO_OutputDate
'end code

... however I get an "invalid qualifier" error. What am I doing wrong? How
can I fix this?

Thanks in advance,
Steve