Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through a Type
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel will not let me type any characters....I can type in Word. | Excel Discussion (Misc queries) | |||
Excel will not let me type any characters....I can type in Word. | Excel Discussion (Misc queries) | |||
Adding new 'Type' to Format->Number->Time->Type | Excel Discussion (Misc queries) | |||
Looping through a Type | Excel Programming | |||
looping through to assign type | Excel Programming |