Need to find a row in a worksheet based on the value of a cell
 

Hi -
My Excel sheet has two columns that represent minimum and maximum
income limits - looks a little like this:
Column A Column B
1 3
4 6
7 10
11 13

and so on up to 3800 or so. I want users to be able to enter a number,
and be taken to the row that corresponds; for example, if a user enters
12, I want them to be taken to the row where column A = 11 and column
B=13.
I've managed to come up with the VLOOKUP statement to find the closest
matching number in column A (VLOOKUP("data entry cell",A3:B1162, 1,
TRUE)), but I can't figure out how to use the result of the VLOOKUP in
a Find function to take the user to the appropriate row.
Any help would be appreciated.
Ginger


--
Ginger
------------------------------------------------------------------------
Ginger's Profile: http://www.excelforum.com/member.php...o&userid=27034
View this thread: http://www.excelforum.com/showthread...hreadid=442769

Worksheet functions can't change selections. You'll need an event macro
to do that.

One way:

Put this in the worksheet code module (right-click the worksheet tab and
choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim nMax As Long
nMax = Cells(Rows.Count, 2).End(xlUp).Value
With Target
If .Address(False, False) = "D1" Then _
If IsNumeric(.Value) Then _
If .Value = 1 And .Value <= nMax Then _
Cells(Application.Match(.Value, Range("A:A"), _
True), 1).EntireRow.Select
End With
End Sub

This assumes that cell D1 is the user entry cell - modify to suit.


In article ,
Ginger wrote:

Hi -
My Excel sheet has two columns that represent minimum and maximum
income limits - looks a little like this:
Column A Column B
1 3
4 6
7 10
11 13

and so on up to 3800 or so. I want users to be able to enter a number,
and be taken to the row that corresponds; for example, if a user enters
12, I want them to be taken to the row where column A = 11 and column
B=13.
I've managed to come up with the VLOOKUP statement to find the closest
matching number in column A (VLOOKUP("data entry cell",A3:B1162, 1,
TRUE)), but I can't figure out how to use the result of the VLOOKUP in
a Find function to take the user to the appropriate row.
Any help would be appreciated.
Ginger