Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2015-01-12
2
Medium Priority
?
175 Views
Last Modified: 2015-01-21
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
0
Comment
Question by:cowboywm
2 Comments
 
LVL 53

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 40546030
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
 

Author Closing Comment

by:cowboywm
ID: 40562758
This solution works great!  Thank you, Rgonzo1971.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Steps to fix “Unable to mount database. (hr=0x80004005, ec=1108)”.
Steps to fix error: “Couldn’t mount the database that you specified. Specified database: HU-DB; Error code: An Active Manager operation fail”
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question