Macro driven record locator.

[Malcolm McMaster]

I have a large worksheet containing around 1000 rows of data.Column "A"
contains a unique ID code for each row.I would like to be able to quickly
locate a particular record (Row) by entering the ID code in a cell then
navigate to it by clicking on a button ( coded with a macro) to take the user
to the location specified by the cell contents.
Does anyone have any ideas. Thanksi n advance

Malcolm McMaster

 

[RobN]

Malcolm,

Maybe something like this will do what you want. Just change D1 to the
column that holds the IDs.

Private Sub CommandButton1_Click()
n = ActiveCell.Value
Range("D1").Activate
Cells.Find(What:=n, After:=ActiveCell, LookIn:=xlValues, Lookat:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
End Sub

A variation on that is as follows. (This will find the cell after the ID
has been typd and the double click that cell. Again just change D1 to the
required column.) You could actually have both procedures so that the user
can employ either method.

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
n = ActiveCell.Value
Range("D1").Activate
Cells.Find(What:=n, After:=ActiveCell, LookIn:=xlValues, Lookat:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Cancel = True
End Sub

Rob