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

G

Ginger

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
 
J

JE McGimpsey

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top