MrExcel Publishing
Your One Stop for Excel Tips & Solutions

A formula that takes an input number to a range?

Posted by Larry Herrold on September 19, 2000 6:48 AM

I have a large spreadsheet that I would like to have the user type in a number and then automatically be taken to that range/cell in the spreadsheet. I'm not sure if I need a macro or formula or what type of box. Any help would be greatly appreciated. Thanks.


Posted by Michael Liu on September 19, 0100 11:01 AM

Do you mean like the users would enter "C25" and be taken to that cell?
Hit the F5 key and see if that suits your needs.

Posted by Larry Herrold on September 19, 0100 11:59 AM

Let me try to explain it better. I was thinking of something like a cell or box where the user inputs a specific id # for example "1001". This number would also be contained in column A of the spreadsheet. I would like the user to enter the number then click on a button "go" and be taken to that cell with the same number. There are more than 4000 lines on this sheet so I'm just trying to make it easier for the user to get to their section. There must be a macro that can be used for this?

Posted by Ivan Moala on September 19, 0100 12:43 PM

Larry, you could try something like this:
Note: assumes
1) ID in Column A (given by you)
2) ID's are numbers (Given by you)
3) ID's are different
4) ID to look for is entered in cell B1
5) To make the entering of the ID easier
for the user Freeze the top row, that way
the user will always have access to B1.

In your sheets code module put this in;

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$B$1" Then
GoToID (Target.Value)
End If
End Sub

In a Prg Module put this code in;

Sub GoToID(ID)
Dim oRow As Double

On Error Resume Next
oRow = Columns(1).Find(ID)
If Err.Number <> 0 Then MsgBox "No match!": End
Application.Goto Cells(oRow, 1), True
End Sub

So that your user selects the ID code @ B1
the worksheet change event is triggered @ B1
and the GoToID is run which will take you here.