popluating a list box without having duplicates
 

I have a list box that i want to populate from an Excel list. The list
would look something like this:

A B
Domain A CompName1
Domain A CompName2
Domain A CompName3
Domain A CompName4
Domain B CompName5
Domain B CompName6
Domain C CompName7

Here is the code I am using:

'begin looking for new list items
Dim RowIndex As Long
Dim StartRow As Long
Dim LastRow As Long
Dim curCell As Range

StartRow = 20

'LastDataRow is a UDF i made that finds the last row with an
entry in it.
LastRow = LastDataRow("DomainDatabase", 1, StartRow)

For RowIndex = StartRow To LastRow
Set curCell = Worksheets("DomainDatabase").Cells(RowIndex,
1)

'check to see if the list is empty
If Me.lstSeriesName.ListCount = 0 Then
'if it is, add the first item to the list
Me.lstSeriesName.AddItem curCell.Value
Else
'If the list isn't empty, find out if the value
' in the cell is already in the listbox
Dim index As Integer
Dim EntryCount As Integer

EntryCount = Me.lstSeriesName.ListCount

For index = 0 To EntryCount
Me.lstSeriesName.ListIndex = index
If Not curCell.Value = Me.lstSeriesName.Value Then
Me.lstSeriesName.AddItem curCell.Value
EntryCount = EntryCount + 1
End If
Next index
End If
Next RowIndex

The problem I have is basically the first domain set will resolve
properly, while the second and subsequent domains will have multiple
listings. The problem appears to be in the following area:

For index = 0 To EntryCount
Me.lstSeriesName.ListIndex = index
If Not curCell.Value = Me.lstSeriesName.Value Then
Me.lstSeriesName.AddItem curCell.Value
EntryCount = EntryCount + 1
End If
Next index

Any help here would be great, I am fairly new to loops much less nested
loops.

Thanks

popluating a list box without having duplicates
 

Thanks Tom for writing this up, it works perfectly!!

On Jan 10, 11:24 am, Tom Ogilvy
wrote:
Dim bFound as Boolean

If Me.lstSeriesName.ListCount = 0 Then
'if it is, add the first item to the list
Me.lstSeriesName.AddItem curCell.Value
Else
bFound = False
For index = 0 To Me.lstSeriesName.Listcount - 1

If curCell.Value = Me.lstSeriesName.List(index) Then
bFound = True
exit for
end if
Next index
if not bFound then
Me.lstSeriesName.AddItem curCell.Value
end if
End if

--
Regards,
Tom Ogilvy



" wrote:
I have a list box that i want to populate from an Excel list. The list
would look something like this:

A B
Domain A CompName1
Domain A CompName2
Domain A CompName3
Domain A CompName4
Domain B CompName5
Domain B CompName6
Domain C CompName7

Here is the code I am using:

'begin looking for new list items
Dim RowIndex As Long
Dim StartRow As Long
Dim LastRow As Long
Dim curCell As Range

StartRow = 20

'LastDataRow is a UDF i made that finds the last row with an
entry in it.
LastRow = LastDataRow("DomainDatabase", 1, StartRow)

For RowIndex = StartRow To LastRow
Set curCell = Worksheets("DomainDatabase").Cells(RowIndex,
1)

'check to see if the list is empty
If Me.lstSeriesName.ListCount = 0 Then
'if it is, add the first item to the list
Me.lstSeriesName.AddItem curCell.Value
Else
'If the list isn't empty, find out if the value
' in the cell is already in the listbox
Dim index As Integer
Dim EntryCount As Integer

EntryCount = Me.lstSeriesName.ListCount

For index = 0 To EntryCount
Me.lstSeriesName.ListIndex = index
If Not curCell.Value = Me.lstSeriesName.Value Then
Me.lstSeriesName.AddItem curCell.Value
EntryCount = EntryCount + 1
End If
Next index
End If
Next RowIndex

The problem I have is basically the first domain set will resolve
properly, while the second and subsequent domains will have multiple
listings. The problem appears to be in the following area:

For index = 0 To EntryCount
Me.lstSeriesName.ListIndex = index
If Not curCell.Value = Me.lstSeriesName.Value Then
Me.lstSeriesName.AddItem curCell.Value
EntryCount = EntryCount + 1
End If
Next index

Any help here would be great, I am fairly new to loops much less nested
loops.

Thanks- Hide quoted text -- Show quoted text -