Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading values into an array of User Defined Types | Excel Programming | |||
Checking user defined types for Nothing | Excel Programming | |||
subs or functions with user defined types | Excel Programming | |||
User-defined data type; Error: Only User-defined types... | Excel Programming |