How would I make the result row of a lookup the active row ?

[Jim]

Hello all:
Relatively new to Excel, so I hope this question isn't one that could
be answered in Excel 101.

I have a situation where I would like to determine if a particular
'Case No.' (key column) is in a table; if so, make that row the
active row. If not, then give some sort of message saying that the
Case No. couldn't be found.

I've tried several methods of validating the fact that the number
exist and returning its row index in the table. What I've not found
via ordinary functions and/or do not know how to do, is to take the
returned info and navigate the cursor to the desired row in the
table.

What I've done:

A1010 - Table Column Headings (Case No., Name, Date, Comment)
A1165356 - Possible Table Range
A11:A65356 - 'CaseNo' Range

Sample Table:
Case No. Name Date Comment
123 Joe Blow 1/1/2006 This is a comment
4354 Jane Jones 2/15/2006 Another comment
48314 H. Braddock 2/24/2006 Yet another comment
..
Some of the things I've done to insure the validity of what I want to
do.

C3 - Used as input for the case Number to look for.
D3 - Formula to get the row within the table ( = Match(C3,CaseNo,0) )
E3 - Another formula to ensure that I'm getting data
(=Index(CaseNo,D3)

What I've not been able to find is a way to move the cursor to the
location within the table for the returned info.

Sorry for such a long post, but wanted to provide as much info as
possible. Thanks in advance for any help.

Jim

 

[Christian]

Hi Jim

You can dedicate a cell to trigger the search
In this case cell a1 but you can make it any cell you want

For i = 11 To Rows.Count
If Cells(i, 1) = Cells(1, 1) Then
Cells(i, 1).EntireRow.Select
Exit Sub
End If
Next i
MsgBox prompt:="The Case No. could not be found"

Or with an Inputbox triggered by a button


Private Sub CommandButton1_Click()
Dim CaseNo
CaseNo = Application.InputBox("Input Case No")
For i = 11 To Rows.Count
If Cells(i, 1).Text = CaseNo Then
Cells(i, 1).EntireRow.Select
Exit Sub
End If
Next i
MsgBox prompt:="The Case No. could not be found"

End Sub

I hope this answeres your question

Cheers Christian

 

[Christian]

That should be

Private Sub Worksheet_Change(ByVal Target As Range)
For i = 11 To Rows.Count
If Cells(i, 1) = Cells(1, 1) Then
Cells(i, 1).EntireRow.Select
Exit Sub
End If
Next i
MsgBox prompt:="The Case No. could not be found"

End Sub

 

[Jim]

Christian:

I've tried your first suggestion and it works exactly like what I
want
to happen. Have not tired the above, but will.

I thank you so very much for the input. This just shows that I need
to
hit the books and learn a little vba. Again, thank you!!

Jim