Caroline
Use the MATCH worksheet function to find the position (column in your case).
Then use the Offset property to get to the right row/column. Here's an
example:
Sub FindValue()
Dim lCol As Long
Dim rLuVal As Range
Dim rLuRng As Range
Dim rResult As Range
Const lROW As Long = 2 '3rd arg of hlookup
Set rLuVal = Range("a1") '1st arg of hlookup
Set rLuRng = Range("b34:D85") '2nd arg of hlookup
On Error Resume Next
lCol = Application.WorksheetFunction.Match( _
rLuVal.Value, rLuRng.Rows(1), False)
On Error GoTo 0
If lCol 0 Then
Set rResult = rLuRng(1).Offset(lROW - 1, lCol - 1)
MsgBox rResult.Value & vbTab & rResult.Address
Else
MsgBox "No match found"
End If
End Sub
--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com
caroline wrote:
Hello,
I am trying to get a cell selected based on the Hlookup function in
VBA: Application.WorksheetFunction.HLookup(Range("a1"),
Range("B34:D85"),2, False) But I can get only the value.
I am sure there is a simple way to do it. I just do not seem to be
able to find it.
Any suggestion would be welcome.
Thanks