Solved

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

Posted on 2015-01-12
2
163 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Developer portfolios can be a bit of an enigma—how do you present yourself to employers without burying them in lines of code?  A modern portfolio is more than just work samples, it’s also a statement of how you work.
In this article I discuss my selections of the Top Four free Outlook OST File Viewers available. Open, view and read even damaged OST files by using these tools. They all provide a clear preview of all data such as emails, notes, tasks, calendars, e…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

617 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