How to use an input box variable in an Excel search function

How can you combine a variable with Excel 2007 Search function?  I do not want the user to run the search directly so I want to search built by what he/she enters into an input box.   Below is a sample sheet and macro.

                       Column A
Row1            TAR NBR
Row2            1
Row3            2
Row4            3
Row 5            4
Row 6            5
Row 7            6
Row 8            7
Row 9            8
Row 10              9
Row 11            10
Row 12            11

  Range("A1").Select
    Dim MyValue As Integer
    Dim TARNbr As Integer
    Prompt = "Search for which TAR?"
    Caption = "Tell me..."
    TARNbr = InputBox(Prompt, Caption, MyValue)
    Cells.Find(What:="TARNbr", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Activate
cowboywmAsked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,

pls try

    Range("A1").Select
    Dim MyValue As Integer
    Dim TARNbr As Integer
    Prompt = "Search for which TAR?"
    Caption = "Tell me..."
    TARNbr = InputBox(Prompt, Caption, MyValue)
    Cells.Find(What:=TARNbr, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Activate

Open in new window

Regards
0
 
cowboywmAuthor Commented:
This solution works great!  Thank you, Rgonzo1971.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.