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

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

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

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