Object instead of value using HLookup

[caroline]

Hello,
I am trying to get a cell selected based on the Hlookup function in VBA:
Application.WorksheetFunction.HLookup(Range("a1"), Range("B3485"),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

 

[Vacation's Over]

worksheetfunction match returns the cell address you are looking for instead
of the value

 

[Dick Kusleika]

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("b3485") '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

 

[caroline]

Thanks a lot to both of you.
--
caroline

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("b3485") '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