Object instead of value using HLookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Thanks a lot to both of you.
--
caroline


Dick Kusleika said:
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
Click to expand...
 
Back
Top