How to work with the Lookups retuning #N/A in VBA?

[prabha_friend]

On Error GoTo HlookupFails
            If Application.WorksheetFunction.HLookup(cell.Offset(0, 1), Hierarchy_Sheet.UsedRange.Rows(1), 1, False) Then  'Currently getting error for #N/A values

Is there a way to make it work without letting in err?

 

[Domenic]

If you use the HLookup method of the Application object instead of the WorksheetFunction object, you'll get a non-breaking error for which you can test using IsError...

[COLOR=darkblue]Dim[/COLOR] vReturnVal [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]

vReturnVal = Application.HLookup(cell.Offset(0, 1), Hierarchy_Sheet.UsedRange.Rows(1), 1, [COLOR=darkblue]False[/COLOR])

[COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] IsError(vReturnVal) [COLOR=darkblue]Then[/COLOR]
    [COLOR=green]'Do something[/COLOR]
[COLOR=darkblue]Else[/COLOR]
    [COLOR=green]'Do something[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

Hope this helps!