Search Goto Feature

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

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

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Bob" <Bob@discussions.microsoft.com> wrote in message
news:2F625DEF-C8DD-4004-A45A-EFD4881D090D@microsoft.com...
>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, 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" wrote:

> 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
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "Bob" <Bob@discussions.microsoft.com> wrote in message
> news:2F625DEF-C8DD-4004-A45A-EFD4881D090D@microsoft.com...
> >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
>
>
>

columns("b") or columns(2)


--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Bob" <Bob@discussions.microsoft.com> wrote in message
news:4BE4A567-7C0D-4BEC-8EF8-B3855EA022E5@microsoft.com...
> 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" wrote:
>
>> 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
>>
>> --
>> Don Guillett
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "Bob" <Bob@discussions.microsoft.com> wrote in message
>> news:2F625DEF-C8DD-4004-A45A-EFD4881D090D@microsoft.com...
>> >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
>>
>>
>>

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

"Don Guillett" wrote:

> columns("b") or columns(2)
>
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "Bob" <Bob@discussions.microsoft.com> wrote in message
> news:4BE4A567-7C0D-4BEC-8EF8-B3855EA022E5@microsoft.com...
> > 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" wrote:
> >
> >> 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
> >>
> >> --
> >> Don Guillett
> >> SalesAid Software
> >> dguillett1@austin.rr.com
> >> "Bob" <Bob@discussions.microsoft.com> wrote in message
> >> news:2F625DEF-C8DD-4004-A45A-EFD4881D090D@microsoft.com...
> >> >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
> >>
> >>
> >>
>
>
>

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


--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Bob" <Bob@discussions.microsoft.com> wrote in message
news:583D22C4-F85C-420C-8AD5-3BBFD89FAB2A@microsoft.com...
> The code stops on this line every time. Any suggestions?
>
> "Don Guillett" wrote:
>
>> columns("b") or columns(2)
>>
>>
>> --
>> Don Guillett
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "Bob" <Bob@discussions.microsoft.com> wrote in message
>> news:4BE4A567-7C0D-4BEC-8EF8-B3855EA022E5@microsoft.com...
>> > 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" wrote:
>> >
>> >> 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
>> >>
>> >> --
>> >> Don Guillett
>> >> SalesAid Software
>> >> dguillett1@austin.rr.com
>> >> "Bob" <Bob@discussions.microsoft.com> wrote in message
>> >> news:2F625DEF-C8DD-4004-A45A-EFD4881D090D@microsoft.com...
>> >> >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 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" wrote:

> 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
>
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "Bob" <Bob@discussions.microsoft.com> wrote in message
> news:583D22C4-F85C-420C-8AD5-3BBFD89FAB2A@microsoft.com...
> > The code stops on this line every time. Any suggestions?
> >
> > "Don Guillett" wrote:
> >
> >> columns("b") or columns(2)
> >>
> >>
> >> --
> >> Don Guillett
> >> SalesAid Software
> >> dguillett1@austin.rr.com
> >> "Bob" <Bob@discussions.microsoft.com> wrote in message
> >> news:4BE4A567-7C0D-4BEC-8EF8-B3855EA022E5@microsoft.com...
> >> > 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" wrote:
> >> >
> >> >> 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
> >> >>
> >> >> --
> >> >> Don Guillett
> >> >> SalesAid Software
> >> >> dguillett1@austin.rr.com
> >> >> "Bob" <Bob@discussions.microsoft.com> wrote in message
> >> >> news:2F625DEF-C8DD-4004-A45A-EFD4881D090D@microsoft.com...
> >> >> >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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Bob" <Bob@discussions.microsoft.com> wrote in message
news:31EA728E-7A7B-457A-9EFE-7A9BDC990060@microsoft.com...
> 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" wrote:
>
>> 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
>>
>>
>> --
>> Don Guillett
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "Bob" <Bob@discussions.microsoft.com> wrote in message
>> news:583D22C4-F85C-420C-8AD5-3BBFD89FAB2A@microsoft.com...
>> > The code stops on this line every time. Any suggestions?
>> >
>> > "Don Guillett" wrote:
>> >
>> >> columns("b") or columns(2)
>> >>
>> >>
>> >> --
>> >> Don Guillett
>> >> SalesAid Software
>> >> dguillett1@austin.rr.com
>> >> "Bob" <Bob@discussions.microsoft.com> wrote in message
>> >> news:4BE4A567-7C0D-4BEC-8EF8-B3855EA022E5@microsoft.com...
>> >> > 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" wrote:
>> >> >
>> >> >> 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
>> >> >>
>> >> >> --
>> >> >> Don Guillett
>> >> >> SalesAid Software
>> >> >> dguillett1@austin.rr.com
>> >> >> "Bob" <Bob@discussions.microsoft.com> wrote in message
>> >> >> news:2F625DEF-C8DD-4004-A45A-EFD4881D090D@microsoft.com...
>> >> >> >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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

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" wrote:

> Surely you tried it and found that if you enter a number that is not there
> nothing happens.
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "Bob" <Bob@discussions.microsoft.com> wrote in message
> news:31EA728E-7A7B-457A-9EFE-7A9BDC990060@microsoft.com...
> > 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" wrote:
> >
> >> 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
> >>
> >>
> >> --
> >> Don Guillett
> >> SalesAid Software
> >> dguillett1@austin.rr.com
> >> "Bob" <Bob@discussions.microsoft.com> wrote in message
> >> news:583D22C4-F85C-420C-8AD5-3BBFD89FAB2A@microsoft.com...
> >> > The code stops on this line every time. Any suggestions?
> >> >
> >> > "Don Guillett" wrote:
> >> >
> >> >> columns("b") or columns(2)
> >> >>
> >> >>
> >> >> --
> >> >> Don Guillett
> >> >> SalesAid Software
> >> >> dguillett1@austin.rr.com
> >> >> "Bob" <Bob@discussions.microsoft.com> wrote in message
> >> >> news:4BE4A567-7C0D-4BEC-8EF8-B3855EA022E5@microsoft.com...
> >> >> > 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" wrote:
> >> >> >
> >> >> >> 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
> >> >> >>
> >> >> >> --
> >> >> >> Don Guillett
> >> >> >> SalesAid Software
> >> >> >> dguillett1@austin.rr.com
> >> >> >> "Bob" <Bob@discussions.microsoft.com> wrote in message
> >> >> >> news:2F625DEF-C8DD-4004-A45A-EFD4881D090D@microsoft.com...
> >> >> >> >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
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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.

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Bob" <Bob@discussions.microsoft.com> wrote in message
news:E3E55795-8FED-4420-8DDB-C0524A61074F@microsoft.com...
>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" wrote:
>
>> Surely you tried it and found that if you enter a number that is not
>> there
>> nothing happens.
>>
>> --
>> Don Guillett
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "Bob" <Bob@discussions.microsoft.com> wrote in message
>> news:31EA728E-7A7B-457A-9EFE-7A9BDC990060@microsoft.com...
>> > 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" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >> --
>> >> Don Guillett
>> >> SalesAid Software
>> >> dguillett1@austin.rr.com
>> >> "Bob" <Bob@discussions.microsoft.com> wrote in message
>> >> news:583D22C4-F85C-420C-8AD5-3BBFD89FAB2A@microsoft.com...
>> >> > The code stops on this line every time. Any suggestions?
>> >> >
>> >> > "Don Guillett" wrote:
>> >> >
>> >> >> columns("b") or columns(2)
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Don Guillett
>> >> >> SalesAid Software
>> >> >> dguillett1@austin.rr.com
>> >> >> "Bob" <Bob@discussions.microsoft.com> wrote in message
>> >> >> news:4BE4A567-7C0D-4BEC-8EF8-B3855EA022E5@microsoft.com...
>> >> >> > 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" wrote:
>> >> >> >
>> >> >> >> 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
>> >> >> >>
>> >> >> >> --
>> >> >> >> Don Guillett
>> >> >> >> SalesAid Software
>> >> >> >> dguillett1@austin.rr.com
>> >> >> >> "Bob" <Bob@discussions.microsoft.com> wrote in message
>> >> >> >> news:2F625DEF-C8DD-4004-A45A-EFD4881D090D@microsoft.com...
>> >> >> >> >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
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>