Looping through a Type

I have a formula that pulls into an array (icolumnpos) the column position on
a sheet. This array is set as a User Defined Type. (icolumnpos.sName or
icolumnpos.iValue).

I also have an array with information, using a similar but different named
user defined type, to store information based on category (rArchive(1).sName
or rArchive(54).ivalue).

I have code at this point that enters information into the sheet, matching
up column position to the array, but it is all hardcoded. I have 95 lines of
code, each telling the code to put this array result into this column. The
only difference between each line is the .sName or .ivalue portions, and they
are the same on both sides of the equation for each. Example:

cells(iwparray,icolumnpos.sname).value = rarchive(inewwp).sname

The for next statement increases iwparray and inewwp each loop through.

I was trying to write something like this:

dim vcell as variant

For Each vCell In icolumnpos
Worksheets("Workpackages").Cells(iWPArray,
iColumnPos.vCell).Value = rArchive(iNewWP).vCell
Next

but this doesn't work. Any suggestions on hwo to make this looping statement
work rather than write the line 95 some times? Any help would be most
appreciated!!! Thanks.
--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

I'm doubtful you can do what you want with a User Defined Type. Is there
any possibility that a 2 dimensional array would work? Say
rArchive(1).sName is rArchive(1)(1) and rArchive(1).iValue is
rArchive(1)(2), etc., out through rArchive(1)(95) and then on to
rArchive(2)(1), etc, if I follow you.

--
Jim
"J Streger" <[email protected]> wrote in message
news:[email protected]...
|I have a formula that pulls into an array (icolumnpos) the column position
on
| a sheet. This array is set as a User Defined Type. (icolumnpos.sName or
| icolumnpos.iValue).
|
| I also have an array with information, using a similar but different named
| user defined type, to store information based on category
(rArchive(1).sName
| or rArchive(54).ivalue).
|
| I have code at this point that enters information into the sheet, matching
| up column position to the array, but it is all hardcoded. I have 95 lines
of
| code, each telling the code to put this array result into this column. The
| only difference between each line is the .sName or .ivalue portions, and
they
| are the same on both sides of the equation for each. Example:
|
| cells(iwparray,icolumnpos.sname).value = rarchive(inewwp).sname
|
| The for next statement increases iwparray and inewwp each loop through.
|
| I was trying to write something like this:
|
| dim vcell as variant
|
| For Each vCell In icolumnpos
| Worksheets("Workpackages").Cells(iWPArray,
| iColumnPos.vCell).Value = rArchive(iNewWP).vCell
| Next
|
| but this doesn't work. Any suggestions on hwo to make this looping
statement
| work rather than write the line 95 some times? Any help would be most
| appreciated!!! Thanks.
| --
| *********************
| J Streger
| MS Office Master 2000 ed.
| MS Project White Belt 2003
|

I'm not sure if I'd use the UDT's, I think I'd prefer
some simple classes with public member variables <G>
but here goes:

you cannot use a for each.. which needs a variant or object,
and a UDT cannot be 'coerced', so you'd need to loop on the bounds of
the arrays.

Option Explicit

Type cp
sName As String
iValue As Long
End Type
Type ap
sName As String
iValue As Long
End Type


Sub LOOPING()
Dim arrCP(10) As cp
Dim arrAP(10) As ap
Dim n&
Dim rArchive As Range

'dummy code to fill the arrays
Set rArchive = Worksheets(2).Cells(1)
For n = LBound(arrCP) To UBound(arrCP)
arrCP(n).iValue = n + 1
arrAP(n).iValue = (n + 1) * 3
Next

'the loop
For n = LBound(arrCP) To UBound(arrCP)
Worksheets(1).Cells(3, arrCP(n).iValue) = rArchive.Cells(1,
arrAP(n).iValue)
Next


End Sub





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


J Streger wrote :

> I have a formula that pulls into an array (icolumnpos) the column
> position on a sheet. This array is set as a User Defined Type.
> (icolumnpos.sName or icolumnpos.iValue).
>
> I also have an array with information, using a similar but different
> named user defined type, to store information based on category
> (rArchive(1).sName or rArchive(54).ivalue).
>
> I have code at this point that enters information into the sheet,
> matching up column position to the array, but it is all hardcoded. I
> have 95 lines of code, each telling the code to put this array result
> into this column. The only difference between each line is the .sName
> or .ivalue portions, and they are the same on both sides of the
> equation for each. Example:
>
> cells(iwparray,icolumnpos.sname).value = rarchive(inewwp).sname
>
> The for next statement increases iwparray and inewwp each loop
> through.
>
> I was trying to write something like this:
>
> dim vcell as variant
>
> For Each vCell In icolumnpos
> Worksheets("Workpackages").Cells(iWPArray,
> iColumnPos.vCell).Value = rArchive(iNewWP).vCell
> Next
>
> but this doesn't work. Any suggestions on hwo to make this looping
> statement work rather than write the line 95 some times? Any help
> would be most appreciated!!! Thanks.