On Nov 16, 10:39 am, Frank M wrote:
hello,

i'm working with excel 2000 and i have the next code in my program:

Private Sub cmb1_Click()
myRow = ActiveCell.Row
Dim Bcell As Range
Form1.ListBox1.Clear
For Each Bcell In Range(("A" & myRow) & (":J" & myRow))
If Bcell.Value = "C" Or Bcell.Value = "S" Or Bcell.Value = "I" Then
Form1.ListBox1.AddItem
ThisWorkbook.Sheets("Sheet1").Range(((ConvertToLet ter(Bcell.Column)) & "1"))
End If
Next Bcell
end sub

so basicly it does the following when myrow = 4
it searches for the values C, S or I in the range A4:J4. When one of these
letters is found the value of row 1 in that same column is added into the
listbox. But now i want a check that makes sure that if the value of row 1 is
already in the listbox, it is not added again. I know the code has to come
after If Bcell.Value = "C" Or ... and that it has to loop through the
listbox, but i have no idea what the code has to be. Can any help me with it?

Hi
This is a classic problem we all come across eventually.
Add the items to a collection first, then add those to the list

Private Sub cmb1_Click()
myRow = ActiveCell.Row
Dim Bcell As Range
Dim ListCollection as New Collection, ListValue as Variant, item as
Variant
Form1.ListBox1.Clear
On error resume next
For Each Bcell In Range(("A" & myRow) & (":J" & myRow))
If Bcell.Value = "C" Or Bcell.Value = "S" Or Bcell.Value = "I"
Then
ListValue =
ThisWorkbook.Sheets("Sheet1").Range(((ConvertToLet ter(Bcell.Column)) &
"1"))
ListCollection.Add ListValue, Cstr(ListValue)
End if
Next Bcell
on error goto 0
'now add to listbox
for each Item in ListCollection
Form1.ListBox1.AddItem Item
next item
end sub

The add in a collection creates an error if the item already exists,
and the copy is not added. The "on error" bits catch the errors.
Check John Walkenbach's website for more on this (like sorting the
list) - I think this was his idea first.

regards
Paul