Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nesting For loops
I have a question about nested For-loop syntax...
In this function, I have a named range on my spreadsheet that has 'default' values called 'Select Company' ... Each of the cells defined in that range - that's their starting value... The cells have a pull-down list associated with them that allows the user to select a company from a static list. I'm trying to take advantage of the construct of the For-loop ... For the outer loop, there are 3 'Next Outer' statements ... (2 inside conditionals statements, 1 for the general For condition) code looks like this... Public Function BuildUniqueCompanyList() As Range Dim CellIterOuter As Range Dim CellIterInner As Range Dim MyRange As Range ' This is the holder of unique values For Each CellIterOuter In Range("Company_List") If CellIterOuter.text = "<<Select Company" Then Next CellIterOuter End If For Each CellIterInner In MyRange If CellIterInner.text = CellIterOuter.text Then Next CellIterOuter End If Next CellIterInner MyRange.Insert (CellIterOuter) ' at this point, value is unique Next CellIterOuter BuildUniqueCompanyList = MyRange End Function What I want to do - the 'Next' statements that are wrapped inside the IF-conditions return back to the outer loop construct instead of continuing with the next line of execution. The inner FOR-loop is pretty straightforward; however, what I would like - if the logic encounters a duplicate, stop the search and break / continue with the Outer loop. Is this possible to do in VBA ?? Appreciate any suggestions. Thank you. Chad |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nesting For loops
Hi
In general for i = 1 to n for k = 1 to i If (some condition is met) then Exit For next k next i will jump out of the inner loop and proceed with the next i value. regards Paul ChadF wrote: I have a question about nested For-loop syntax... In this function, I have a named range on my spreadsheet that has 'default' values called 'Select Company' ... Each of the cells defined in that range - that's their starting value... The cells have a pull-down list associated with them that allows the user to select a company from a static list. I'm trying to take advantage of the construct of the For-loop ... For the outer loop, there are 3 'Next Outer' statements ... (2 inside conditionals statements, 1 for the general For condition) code looks like this... Public Function BuildUniqueCompanyList() As Range Dim CellIterOuter As Range Dim CellIterInner As Range Dim MyRange As Range ' This is the holder of unique values For Each CellIterOuter In Range("Company_List") If CellIterOuter.text = "<<Select Company" Then Next CellIterOuter End If For Each CellIterInner In MyRange If CellIterInner.text = CellIterOuter.text Then Next CellIterOuter End If Next CellIterInner MyRange.Insert (CellIterOuter) ' at this point, value is unique Next CellIterOuter BuildUniqueCompanyList = MyRange End Function What I want to do - the 'Next' statements that are wrapped inside the IF-conditions return back to the outer loop construct instead of continuing with the next line of execution. The inner FOR-loop is pretty straightforward; however, what I would like - if the logic encounters a duplicate, stop the search and break / continue with the Outer loop. Is this possible to do in VBA ?? Appreciate any suggestions. Thank you. Chad |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nesting For loops
Public Function BuildUniqueCompanyList() As Range
Dim CellIterOuter As Range Dim CellIterInner As Range Dim bFound as boolean Dim MyRange As Range ' This is the holder of unique values set MyRange = Range("A1") For Each CellIterOuter In Range("Company_List") If CellIterOuter.text < "<<Select Company" Then bfound = False For Each CellIterInner In MyRange If CellIterInner.text = CellIterOuter.text Then bFound = True exit for End If Next CellIterInner if bfound then MyRange.Insert (CellIterOuter) ' at this point, value is unique end if end if Next CellIterOuter Set MyRange = Range("A1",MyRange) BuildUniqueCompanyList = MyRange End Function You can also look at using a collection or a dictionary object. -- Regards, Tom Ogilvy "ChadF" wrote: I have a question about nested For-loop syntax... In this function, I have a named range on my spreadsheet that has 'default' values called 'Select Company' ... Each of the cells defined in that range - that's their starting value... The cells have a pull-down list associated with them that allows the user to select a company from a static list. I'm trying to take advantage of the construct of the For-loop ... For the outer loop, there are 3 'Next Outer' statements ... (2 inside conditionals statements, 1 for the general For condition) code looks like this... Public Function BuildUniqueCompanyList() As Range Dim CellIterOuter As Range Dim CellIterInner As Range Dim MyRange As Range ' This is the holder of unique values For Each CellIterOuter In Range("Company_List") If CellIterOuter.text = "<<Select Company" Then Next CellIterOuter End If For Each CellIterInner In MyRange If CellIterInner.text = CellIterOuter.text Then Next CellIterOuter End If Next CellIterInner MyRange.Insert (CellIterOuter) ' at this point, value is unique Next CellIterOuter BuildUniqueCompanyList = MyRange End Function What I want to do - the 'Next' statements that are wrapped inside the IF-conditions return back to the outer loop construct instead of continuing with the next line of execution. The inner FOR-loop is pretty straightforward; however, what I would like - if the logic encounters a duplicate, stop the search and break / continue with the Outer loop. Is this possible to do in VBA ?? Appreciate any suggestions. Thank you. Chad |