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:
    453
    Frank Kabel
    May 19, 2004
  2. jiminy_crime
    Replies:
    1
    Views:
    757
    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:
    1,945
    Gord Dibben
    Nov 27, 2004
  4. Daniel
    Replies:
    1
    Views:
    232
    David McRitchie
    Jun 23, 2005
  5. 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:
    342
    M G Henry
    Oct 22, 2008
Loading...

Share This Page