Find text, select row and define selected row

Welcome to the Board!

How 'bout something like this:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> ans <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> NewRange <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    ans = InputBox("Enter search term", "Search for?")<br>    <br>    NewRange = Cells.Find(What:=ans, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _<br>        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _<br>        , SearchFormat:=False).EntireRow.Address<br>        <br>        ActiveWorkbook.Names.Add Name:=ans, RefersToR1C1:=NewRange<br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

No need to select

lenze
just noticed Smitty's take on this. Using his idea, maybe

lenze

Guys - you are geniouses bouth of you - thank you very much

i have one more question - is it possilbe to create a macro that would copy info from cell a1 and name the row with that info, also loop it to do it from row 1 to 3000 (of course to take the info from cell a2 and name the row 2 with that info and so on until reaches row 3000)

again thank you very much for your help

I can't think if why you would want 3000 defined names, but here's a shot:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 3000<br>            Cells(i, "A").EntireRow.Name = Cells(i, "A").Text<br>        <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Note that there's no test for valid range names or duplicates.

Hi, Smitty - thank you for your answer - i got very similar thing - while using it i found a problem - the names that i use most of the time has at least 2 words and excel doesn't allow to use spaces in defining row names so in macro it's asking to replace spaces with _ (underscore) i was wondering if beside replacing spaces you can add "-" character as well - pretty much look for space or - and replace it with _ (underscore) thank you - here is the code:

Sub Macro1()
Dim a As Long
For a = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Cells(a, 26) = Application.WorksheetFunction.Substitute(Cells(a, 1), " ", "_")
ActiveWorkbook.Names.Add Name:=Cells(a, 26), RefersToR1C1:="=Sheet1!R" & a
Next a
End Sub

again thank you very much for your help

actually if it's possilbe to replace all special characters to _ (underscore) if not then just those 2.

thank you

Did the code you posted work?