Object instead of value using HLookup
 

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

Object instead of value using HLookup
 

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

"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
--
caroline

Object instead of value using HLookup
 

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

Object instead of value using HLookup
 

Thanks a lot to both of you.
--
caroline


"Dick Kusleika" wrote:

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