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

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

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