| Home |
| Search |
| Today's Posts |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 2 Label Options - Forms Object vs Control Box Object | Excel Discussion (Misc queries) | |||
| How do I hlookup a text document object and then display it? | Excel Worksheet Functions | |||
| Object Variable Not Set Error on Selection object | Excel Worksheet Functions | |||
| Option button object proeprties or object not found in vba | Excel Programming | |||
| Confusion about how the Window object fits into the Excel object model | Excel Programming | |||
Threaded Mode
