Hello,

I have created a worksheet that has 18000 + rows. There are 550 ID numbers
for my employees.
What I would like to do is create a cell that when I type in a ID# it will
take me to the persons ID# in column "A" where ever it is located. In cell
number 53 or cell number 17200. just to give you a little heads up each
employee has 30 rows of information so there's a gap between ID#'s.
I can use Go To but other people will be using this and they are not a
computer knowledgeable. I need to make this as user friendly as possible.
Basically I need to create a macro or formula that will bring you down to
a ID# in a single column.

"Sample"

Search box |__2__|

A
1
2
3
4

- -
Thank you for all your help!
Glenn

I'm assuming you don't have to worry about duplicate ID numbers, or at least
only want to go to first reference. Right click on your sheet tab, view code,
paste this in. Change the range callouts as appropriate.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
'Change the following callouts as desired
SearchCell = "$A$2"
SearchRange = "A4:A20000"

If Target.Address = SearchCell Then
Range(SearchRange).Select
Selection.Find(What:=Range(SearchCell).Value, After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End If

End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Glenn" wrote:

Hello,

I have created a worksheet that has 18000 + rows. There are 550 ID numbers
for my employees.
What I would like to do is create a cell that when I type in a ID# it will
take me to the persons ID# in column "A" where ever it is located. In cell
number 53 or cell number 17200. just to give you a little heads up each
employee has 30 rows of information so there's a gap between ID#'s.
I can use Go To but other people will be using this and they are not a
computer knowledgeable. I need to make this as user friendly as possible.
Basically I need to create a macro or formula that will bring you down to
a ID# in a single column.

"Sample"

Search box |__2__|

A
1
2
3
4

- -
Thank you for all your help!
Glenn

Like,

Thank you for your response.
I am a true laymen. I have not worked with VBA except for recording a macro
through excel. I am trying to make sense of what you posted.
My first question: SearchCell = "$A$2" Is this the box where you place
the ID# you place the ID you are searching?
The search Range that you posted will work for the range of the ID#'s.

Range(SearchRange).Select
Selection.Find(What:=Range(SearchCell).Value, After:=ActiveCell,

Where you have (SearchRange) and (SearchCell) and (ActiveCell) Do I need to
replace these with the Range,Cell and Active Cell?
One more question do I need to change anything else in the macro you sent
me?
--
Thank You
Glenn


"Luke M" wrote:

I'm assuming you don't have to worry about duplicate ID numbers, or at least
only want to go to first reference. Right click on your sheet tab, view code,
paste this in. Change the range callouts as appropriate.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
'Change the following callouts as desired
SearchCell = "$A$2"
SearchRange = "A4:A20000"

If Target.Address = SearchCell Then
Range(SearchRange).Select
Selection.Find(What:=Range(SearchCell).Value, After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End If

End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Glenn" wrote:

Hello,

I have created a worksheet that has 18000 + rows. There are 550 ID numbers
for my employees.
What I would like to do is create a cell that when I type in a ID# it will
take me to the persons ID# in column "A" where ever it is located. In cell
number 53 or cell number 17200. just to give you a little heads up each
employee has 30 rows of information so there's a gap between ID#'s.
I can use Go To but other people will be using this and they are not a
computer knowledgeable. I need to make this as user friendly as possible.
Basically I need to create a macro or formula that will bring you down to
a ID# in a single column.

"Sample"

Search box |__2__|

A
1
2
3
4

- -
Thank you for all your help!
Glenn