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 your help Richard, but im still having a little trouble with this. Just to clarify, the search range will always be on the same sheet as the search criteria in cell c1. The previous code you provided actually returned every single name on that sheet, it did not match what the crieria was in cell c1.

Looking at the code, the search criteria is given as mystr;
mystr = [c1].Value

but i cant see where in the code you are comparing this to see if it matches a Name...

 

[Richard Schollar]

John

You're right. Not doing very well here am I? So, you want to add the names of the named ranges to the list box if the named range lies in cells Am1:Da5000 AND if the name of the name contains the value in C1?

 

[JohnPoole]

Yes! thats it exactly. I'm not the best at describing the problem, but that would be the right answer!

 

[JulieG]

Hello,
After reading your thread.. i realized this is exactly what i need for a project given to me at work. However, i am not macro enabled!

If either of you wouldnt mind teaching me a little to accomplish setting up this maco on a sheet for myself, i would be greatful.

Thanks,
Julie

 

[JohnPoole]

Hi Julie, in the first instance you will need to enable macros by the sound of it. If your using Excel 2003 then open a blank sheet, Click Tools, Macros, then security. Select medium or low security settings to enable macro usage.

If your using Excel 2007 I think Richard might be better able to help you.

If you have a button on your sheet you could just copy the macro into the button and use that to run it, this would probably be easiest.

 

[JulieG]

I am going to sound macro ignorate here but i am:

So can i just copy/paste the code to a button, assign a control, import my data and perform a search?

It cant be that easy.. This will make life so much easier.

Will you send me the final, working version of the code.

 

[JohnPoole]

If your importing data then im guessing that your not using defined names which is my problem. However, let's wait for Richards response and see if we can work out a solution from there. I think maybe Richards first answer might be what your looking for though.

 

[Richard Schollar]

Right, let's try this:

Public Sub findnames()
Dim c As Range, rng As Range
Dim n As Name, strFirstAdd As String
ActiveSheet.ListBox1.Clear
mystr = [c1].Value
 
Set rng = Range("AM1:DA5000")
 
For Each n In ThisWorkbook.Names
  On Error Resume Next
  Set c = Range(n.Name)
  On Error Goto 0
  If Not c Is Nothing Then
    If c.Parent.Name = rng.Parent.Name Then
      If Not Intersect(c, rng) Is Nothing Then 
        If Instr(1,n.Name,myStr)>0 Then Activesheet.ListBox1.AddItem n.Name
      End If
    End If
  End If
  Set c = Nothing
Next n
End Sub

 

[JohnPoole]

Ho Ho! That did the trick, many thanks for that Richard! Your help with this is greatly appeciated.

Just as a side problem, and this isnt important, but I notice that this fix is case sensitive, is their a simple way to remove the sensitiveness? Don'ty worry about this if their isn't an easy answer

 

[Richard Schollar]

That's dead easy john - just need to add another argument to the Instr function:

Public Sub findnames()
Dim c As Range, rng As Range
Dim n As Name, strFirstAdd As String
ActiveSheet.ListBox1.Clear
mystr = [c1].Value
 
Set rng = Range("AM1:DA5000")
 
For Each n In ThisWorkbook.Names
  On Error Resume Next
  Set c = Range(n.Name)
  On Error Goto 0
  If Not c Is Nothing Then
    If c.Parent.Name = rng.Parent.Name Then
      If Not Intersect(c, rng) Is Nothing Then 
        If Instr(1,n.Name,myStr,vbTextCompare)>0 Then Activesheet.ListBox1.AddItem n.Name
      End If
    End If
  End If
  Set c = Nothing
Next n
End Sub