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(((ConvertToLetter(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?

On Nov 16, 10:39 am, Frank M <Fra...@discussions.microsoft.com> 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(((ConvertToLetter(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(((ConvertToLetter(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

Thanks, this works exactly how i want it

"(E-Mail Removed)" wrote:

> On Nov 16, 10:39 am, Frank M <Fra...@discussions.microsoft.com> 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(((ConvertToLetter(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(((ConvertToLetter(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
>