Hi ladies and gents

I have a question:
How can i lookup a value in a range then select the cell that value

Thanks for the help
Ernie

Ernie,

Have you tried find?

This is also available in VB if you want to write a macro for it.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"ernie" wrote:

Hi ladies and gents

I have a question:
How can i lookup a value in a range then select the cell that value

Thanks for the help
Ernie

Presumably you want to do this in VBA code?

Here's a basic (meaning primitive, not Basic) routine that you could modify
to meet your specific needs. Note that if what you are trying to Find is not
in the list, then an error is generated, which this code takes care of, but
in this case if no match is found then the entire range will remain
highlighted/selected. You also need to be on the sheet where the search is
to be performed before running this macro. Since you said you wanted to
select the found item, I used .Select initially rather than just doing a
search in a virtual range.

Sub SimpleFind()
Dim toBeFound As Variant
'you can use code to
'determine the range
'and use a variable
'instead of fixed range
Range("D1:D9").Select
'you could get value to
'search for from user or
'from another cell
toBeFound = 77
On Error Resume Next ' errors if no match
Selection.Find(What:=toBeFound, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
If Err < 0 Then
Err.Clear
End If
On Error GoTo 0
End Sub


Basic steps:
define/select the range to be searched

"ernie" wrote:

Hi ladies and gents

I have a question:
How can i lookup a value in a range then select the cell that value

Thanks for the help
Ernie