Search Goto Feature

[Guest]

I need a Search box at the top of each sheet in a work book to enter a job
number press enter a goto that number in a column (Job Number) since there
are several hundred in that column.(I do not want to go through the Excel
menu)
What code is necessary to do this? Should I insert a text box or something
else?

Thanks,
Bob

 

[Don Guillett]

right click sheet tab>view code>insert this.
Now when you enter your number in cell c1 and touch the enter key you will
go down that column to the address of the number entered. Change C & 3 to
your column.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$1" Then Exit Sub
Columns(3).Find(Target).Select
End Sub

 

[Guest]

Don, below is what i am using line 3 - Columns(B).Find(Target).Select - does
not work. Why

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$4" Then Exit Sub
Columns(B).Find(Target).Select
End Sub

 

[Don Guillett]

columns("b") or columns(2)

 

[Guest]

The code stops on this line every time. Any suggestions?

 

[Don Guillett]

This will work if you followed instructions to place in the sheet module.
did you put in a regular module?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$1" Then Exit Sub
Columns(2).Find(Target).Select
End Sub

 

[Guest]

Don thanks for your help it works great! What can be added so that when the
number entered is not found it would return to the cell and a message would
say "Not Found" so a new number could be entered again?

 

[Don Guillett]

Surely you tried it and found that if you enter a number that is not there
nothing happens.

 

[Guest]

I have tried it many times if the number is found it works and will go to
that cell, if the number is not found i get the following error:
Run-time error "91"
Object variable or with block variable not set
and the code stops on:
Columns(2).Find(Target).Select

 

[Don Guillett]

Send your workbook to my private email below and I will take a look later
today.
You must copy this and use the same subject line so I will know what to do.
You would be surprised to know that some send a workbook with NO
explanation.