Add search box to Excel

M

Mike

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.
 
G

Gord Dibben

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
 
G

Gord Dibben

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
 
M

Mike

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

Gord Dibben

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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top