Finding text in a string

[Guest]

Hi, Using XL2000

Each cell in Col A contains a random selection of words, such as steel,
brass, aluminium. Comma seperated.
Col B contains user names.
There are upto 500 rows.

The same Words in featured in Col A exist in ComboBox1 in Userform1.
The user selects a word from the list in CB1.

On a click event, I need each row searched and where a text match is found
in each cell in Col A, the contents of Cell B1 are displayed in ListBox1

That is..

At a qualifying row; If A1 contains Steel, Brass, Aluminium AND Steel is
selected from CB1
Listbox1 displays the content of B1

Any VBA ideas appreciated.
Many thanks,

Paul

 

[Tom Ogilvy]

Dim rng as Range, cell as Range
With Userform1
set rng = Range(Cells(1,1),Cells(1,1).End(xldown))
for each cell in rng
if instr(1,cell.Value, .cb1.text, vbTextCompare) then
.listbox1.AddItem cell.offset(0,1)
end if
Next
End With

If you have possible choices such as Steel and Eel, then you would have to
have additional tests.