Excel - Add search box to Excel

Asked By Mik on 19-Jun-08 04:07 PM
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




Gord Dibben replied on 19-Jun-08 04:15 PM
Mike

Try Data>Filter>Autofilter

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

You could automate this with code if you wanted to.


Gord Dibben  MS Excel MVP
Gord Dibben replied on 19-Jun-08 04:18 PM
By the way.

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

Just 205 will be returned.


Gord
Mik replied on 19-Jun-08 04:57 PM
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 replied on 19-Jun-08 05:41 PM
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