Search Names problem

[JohnPoole]

Hi all, I am trying to write a macro which will look at a search term entered into cell C1, then if there is a defined Name in the search range (AM1:DA5000) which matches then then name will be outputted to listbox 1.

eg if cell c1 = Out
then listbox 1 would return the following names from the above range:
Out1
Out2
Outer
Siderout
Outsider

I have the following code, which will only return the first instance it finds:

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> findnames()<br><br><SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range, rng<br><SPAN style="color:#00007F">Dim</SPAN> n <SPAN style="color:#00007F">As</SPAN> Name<br><br>ActiveSheet.ListBox1.Clear<br>mystr = [c1].Value<br>**** <br>****<SPAN style="color:#00007F">Set</SPAN> rng = Range("AM1:DA5000")<br>************<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> rng<br>****************<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> n <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Names<br>********************<SPAN style="color:#00007F">If</SPAN> InStr(c, mystr) > 0 <SPAN style="color:#00007F">Then</SPAN><br>****************<br>********************ActiveSheet.ListBox1.AddItem c<br>****************** <br>************************<br>******************** <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>****************<SPAN style="color:#00007F">Next</SPAN> n<br>************<SPAN style="color:#00007F">Next</SPAN> c<br>********<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


Can anyone advise what I need to change here?

 

[JohnPoole]

Thanks for that Richard, your a star!