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

[Vacation's Over]

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

[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("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

--
**** Kusleika
MVP - Excel
Daily Dose of Excel
http://www.*****-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

[caroline]

Thanks a lot to both of you.
--
caroline


"**** 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
>
> --
> **** Kusleika
> MVP - Excel
> Daily Dose of Excel
> http://www.*****-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
>
>
>