Column Specific Search

[kod2th3e]

I am looking to search for a string within a certain column only and activate that cell. If I have the table below, I want to use an input box to acquire a string from the user and search only column B and activate the cell. If the user enters a string that is not found in column B I'd like a message box to pop up saying that that string wasn't found.

Col A	Col B	Col C
123	951	abc
abc	456	def
951	123	ghi
def	789	123

<tbody>
</tbody>

So far I have the following code:

sub userfind()
dim struserinput as string
on error goto skipmessage
struserinput = InputBox("Please enter the string you would like to find:", "String?") 'gets the user's input to search for

Cells.Find(What:=struserinput, After:=ActiveCell, LookIn:=xlFormulas, _
         LookAt:=xlWhole, SearchOrder:=xlByColumns, Search Direction:=xlNext, _
         MatchCase:=True, SearchFormat:=False).activate

skipmessage:
MsgBox "The string you entered is not located within column B", vbExclamation, "Error.."
end sub

Let's say the user types 123 in the input box. When this happens cell A1 gets activated, I want cell B3 to be activated instead. If the user inputted "abc" in the input box I'd like it to kick out the message box located under the skipmessage line. Thanks in advance for your help.

 

[Peter_SSs]

Try this

<font face=Courier New><br><br><SPAN style="color:#00007F">Sub</SPAN> userfind()<br>    <SPAN style="color:#00007F">Dim</SPAN> struserinput <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rFound <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    struserinput = InputBox("Please enter the string you would like to find:", "String?") <SPAN style="color:#007F00">'gets the user's input to search for</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rFound = Columns("B").Find(What:=struserinput, After:=ActiveCell, LookIn:=xlFormulas, _<br>             LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _<br>             MatchCase:=True, SearchFormat:=False)<br>             <br>    <SPAN style="color:#00007F">If</SPAN> rFound <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "The string you entered is not located within column B", vbExclamation, "Error.."<br>    <SPAN style="color:#00007F">Else</SPAN><br>        rFound.Select<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[Andrew Poulsom]

Instead of Cells.Find use Columns("B").Find. You may need to change the After argument.

 

[BobUmlas]

I am looking to search for a string within a certain column only and activate that cell. If I have the table below, I want to use an input box to acquire a string from the user and search only column B and activate the cell. If the user enters a string that is not found in column B I'd like a message box to pop up saying that that string wasn't found.

Col A	Col B	Col C
123	951	abc
abc	456	def
951	123	ghi
def	789	123

<TBODY>
</TBODY>

So far I have the following code:

sub userfind()
dim struserinput as string
on error goto skipmessage
struserinput = InputBox("Please enter the string you would like to find:", "String?") 'gets the user's input to search for

Cells.Find(What:=struserinput, After:=ActiveCell, LookIn:=xlFormulas, _
         LookAt:=xlWhole, SearchOrder:=xlByColumns, Search Direction:=xlNext, _
         MatchCase:=True, SearchFormat:=False).activate

skipmessage:
MsgBox "The string you entered is not located within column B", vbExclamation, "Error.."
end sub

Let's say the user types 123 in the input box. When this happens cell A1 gets activated, I want cell B3 to be activated instead. If the user inputted "abc" in the input box I'd like it to kick out the message box located under the skipmessage line. Thanks in advance for your help.

----------------
instead of cells.find, use Columns(2).Find

But as you have your code, after the find it's going to go to the Skipmessage code anyway.

 

[AlphaFrog]

Pretty much the same as Peter's with a few mods...

[COLOR=darkblue]Sub[/COLOR] userfind()
    [COLOR=darkblue]Dim[/COLOR] struserinput [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], Found [COLOR=darkblue]As[/COLOR] Range
Retry:
    struserinput = Application.InputBox("Please enter the string you would like to find:", "String?", Type:=2) [COLOR=green]'gets the user's input to search for[/COLOR]
    [COLOR=darkblue]If[/COLOR] struserinput = "False" [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR] [COLOR=green]'User canceled[/COLOR]
    [COLOR=darkblue]Set[/COLOR] Found = Range("B:B").Find(What:=struserinput, LookIn:=xlValues, _
                                   LookAt:=xlWhole, SearchOrder:=xlByRows, _
                                   SearchDirection:=xlNext, MatchCase:=True)
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Found [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        Found.Select
    [COLOR=darkblue]Else[/COLOR]
        MsgBox "The string ' " & struserinput & "' is not located within column B ", vbExclamation, "String Not Found"
        [COLOR=darkblue]GoTo[/COLOR] Retry
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]


End [COLOR=darkblue]Sub[/COLOR]

Note: If you use optional argument After:=ActiveCell in the .Find method and the Active cell is not within the search range (column B) , the code will error.

 

[kod2th3e]

Try this



Sub userfind()
Dim struserinput As String
Dim rFound As Range

struserinput = InputBox("Please enter the string you would like to find:", "String?") 'gets the user's input to search for

Set rFound = Columns("B").Find(What:=struserinput, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False)

If rFound Is Nothing Then
MsgBox "The string you entered is not located within column B", vbExclamation, "Error.."
Else
rFound.Select
End If
End Sub

This seem to work as long as I had a cell in the column selected first, thanks for the help.

 

[kod2th3e]

----------------
instead of cells.find, use Columns(2).Find

But as you have your code, after the find it's going to go to the Skipmessage code anyway.

You're correct, I forgot to put exit sub in what I submitted after it activates the cell.

 

[kod2th3e]

Instead of Cells.Find use Columns("B").Find. You may need to change the After argument.

This also works if I have an already active cell in column B.

 

[kod2th3e]

These are all great suggestions and seem to work well. Thanks for your time and input in helping me to solve the problem I was facing, I greatly appreciate it.

Best Regards,
Cody

 

[AlphaFrog]

This also works if I have an already active cell in column B.

Remove the optional After:=ActiveCell argument and you don't have to already have the Activecell in column B.