Add search box to Excel

Discussion in 'Microsoft Excel Discussion' started by Mike, Jun 19, 2008.

  1. Mike

    Mike Guest

    First, I am a very novice Excel user. I have a 2000+ row worksheet that my
    boss would like me to add a search box for. Each cell entry in this column in
    a unique account number. Ctrl-f does not work because my account numbers
    begin at 1 and end at 10000 (205 could be returned from multiple cells 2051
    and 2052...). This is joined by 7 other related columns that display YTD data
    on the account. Basically I need to enter the account number in the search
    box and have the whole row of data appear at the top of the range for that
    account. I think this may have to be done in VBA and that is over my head.
    Any assistance would be greatly appreciated.
    --
    Mike
     
    Mike, Jun 19, 2008
    #1
    1. Advertisements

  2. Mike

    Gord Dibben Guest

    Mike

    Try Data>Filter>Autofilter

    Either select the account number by scroll or by "Custom" and type the number in
    "equal to"

    You could automate this with code if you wanted to.


    Gord Dibben MS Excel MVP

    On Thu, 19 Jun 2008 13:07:00 -0700, Mike <> wrote:

    >First, I am a very novice Excel user. I have a 2000+ row worksheet that my
    >boss would like me to add a search box for. Each cell entry in this column in
    >a unique account number. Ctrl-f does not work because my account numbers
    >begin at 1 and end at 10000 (205 could be returned from multiple cells 2051
    >and 2052...). This is joined by 7 other related columns that display YTD data
    >on the account. Basically I need to enter the account number in the search
    >box and have the whole row of data appear at the top of the range for that
    >account. I think this may have to be done in VBA and that is over my head.
    >Any assistance would be greatly appreciated.
     
    Gord Dibben, Jun 19, 2008
    #2
    1. Advertisements

  3. Mike

    Gord Dibben Guest

    By the way.

    When using CTRL + f you can set options to "match entire cells contents" so you
    don't pick up all the cells that contain 205.

    Just 205 will be returned.


    Gord

    On Thu, 19 Jun 2008 13:15:32 -0700, Gord Dibben <gorddibbATshawDOTca> wrote:

    >Mike
    >
    >Try Data>Filter>Autofilter
    >
    >Either select the account number by scroll or by "Custom" and type the number in
    >"equal to"
    >
    >You could automate this with code if you wanted to.
    >
    >
    >Gord Dibben MS Excel MVP
    >
    >On Thu, 19 Jun 2008 13:07:00 -0700, Mike <> wrote:
    >
    >>First, I am a very novice Excel user. I have a 2000+ row worksheet that my
    >>boss would like me to add a search box for. Each cell entry in this column in
    >>a unique account number. Ctrl-f does not work because my account numbers
    >>begin at 1 and end at 10000 (205 could be returned from multiple cells 2051
    >>and 2052...). This is joined by 7 other related columns that display YTD data
    >>on the account. Basically I need to enter the account number in the search
    >>box and have the whole row of data appear at the top of the range for that
    >>account. I think this may have to be done in VBA and that is over my head.
    >>Any assistance would be greatly appreciated.
     
    Gord Dibben, Jun 19, 2008
    #3
  4. Mike

    Mike Guest

    Could you give me any tips with doing this in code? The workbook needs to be
    simple to use and tidy looking.
    --
    Mike


    "Gord Dibben" wrote:

    > By the way.
    >
    > When using CTRL + f you can set options to "match entire cells contents" so you
    > don't pick up all the cells that contain 205.
    >
    > Just 205 will be returned.
    >
    >
    > Gord
    >
    > On Thu, 19 Jun 2008 13:15:32 -0700, Gord Dibben <gorddibbATshawDOTca> wrote:
    >
    > >Mike
    > >
    > >Try Data>Filter>Autofilter
    > >
    > >Either select the account number by scroll or by "Custom" and type the number in
    > >"equal to"
    > >
    > >You could automate this with code if you wanted to.
    > >
    > >
    > >Gord Dibben MS Excel MVP
    > >
    > >On Thu, 19 Jun 2008 13:07:00 -0700, Mike <> wrote:
    > >
    > >>First, I am a very novice Excel user. I have a 2000+ row worksheet that my
    > >>boss would like me to add a search box for. Each cell entry in this column in
    > >>a unique account number. Ctrl-f does not work because my account numbers
    > >>begin at 1 and end at 10000 (205 could be returned from multiple cells 2051
    > >>and 2052...). This is joined by 7 other related columns that display YTD data
    > >>on the account. Basically I need to enter the account number in the search
    > >>box and have the whole row of data appear at the top of the range for that
    > >>account. I think this may have to be done in VBA and that is over my head.
    > >>Any assistance would be greatly appreciated.

    >
    >
     
    Mike, Jun 19, 2008
    #4
  5. Mike

    Gord Dibben Guest

    You could use the Macro Recorder to get some code.

    Or something similar to this.

    Sub filtering()
    Dim whatfind As String
    ActiveSheet.AutoFilterMode = False
    whatfind = InputBox("enter a code number")
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:=whatfind
    End Sub

    Assumes Column A is code number column with a title in A1.

    For more code for autofiltering see Ron de Bruin's site for examples.

    http://www.rondebruin.nl/copy5.htm


    Gord

    On Thu, 19 Jun 2008 13:57:04 -0700, Mike <> wrote:

    >Could you give me any tips with doing this in code? The workbook needs to be
    >simple to use and tidy looking.
     
    Gord Dibben, Jun 19, 2008
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Roger1947
    Replies:
    1
    Views:
    511
    Frank Kabel
    May 19, 2004
  2. jiminy_crime
    Replies:
    1
    Views:
    811
    djssuk
    Jul 16, 2004
  3. Broida (spamless)

    How can I add drop-down box in Excel 2002?

    Broida (spamless), Nov 26, 2004, in forum: Microsoft Excel Discussion
    Replies:
    4
    Views:
    2,144
    Gord Dibben
    Nov 27, 2004
  4. Guest

    How to search and add a value from sheet1 to sheet2

    Guest, Mar 7, 2005, in forum: Microsoft Excel Discussion
    Replies:
    1
    Views:
    317
    Gord Dibben
    Mar 7, 2005
  5. Daniel
    Replies:
    1
    Views:
    339
    David McRitchie
    Jun 23, 2005
  6. Tijs

    search for numbers with file/open/tools/search

    Tijs, Aug 26, 2008, in forum: Microsoft Excel Discussion
    Replies:
    0
    Views:
    253
  7. M G Henry

    Enable Combo box based on value in check box and list box....

    M G Henry, Oct 22, 2008, in forum: Microsoft Excel Discussion
    Replies:
    0
    Views:
    379
    M G Henry
    Oct 22, 2008
  8. Koen

    Excel 2003 add List to Combo Box from Control Toolbox

    Koen, Apr 28, 2010, in forum: Microsoft Excel Discussion
    Replies:
    1
    Views:
    493
    ozgrid.com
    Apr 28, 2010
Loading...