Lookup problems

[cLiffordiL]

Anyone who'd encountered problems when they encapsulate VLookup or
HLookup? I'm trying to write a generalized lookup function which I can
control the direction of lookup through an argument but when I step the code
through, it stops execution at the line of .VLookup/ .HLookup. It does not
halt the program flow and execute all cells that uses this formula with
#VALUE! result.

Public Function MyGeneralLookUp(Direction As Long, Key As Range, TableName
As Range, StartPoint As Long, Match As Boolean) As String
MyGeneralLookUp = ""
Application.EnableEvents = False
' Some key to search with
If (Not (CStr(Key.Value) = "")) Then
' Initialize an offset table so that data can be retrieved from the
correct position
Call CreateOffsetTable
' Calculate offset distance off StartPoint
Offset = SeekOffset(OffsetTag)
With Application.WorksheetFunction
' Enforce looking up exact key
Match = (Not Match)
' Lookup value depending on direction
Select Case Direction
Case HDir
FetchedResult = .VLookup(Key, TableName, Offset, Match)
Case VDir
FetchedResult = .HLookup(Key, TableName, Offset, Match)
End Select
' No error from lookup & result is either a text or number
If ((Not .IsError(FetchedResult)) And _
(((.IsText(FetchedResult)) Or (.IsNumber(FetchedResult)))) _
) Then
MyGeneralLookUp = FetchedResult
End If
End With
End If
Application.EnableEvents = True
End Function

Would appreciate any suggestions or pointers! Cheers!
_______
cLiffordiL

 

[Niek Otten]

What do CreateOffsetTable and SeekOffset do? (include code)
What are the types and values of OffsetTag, Offset, HDir, Vdir (especially Offset, since it is part of the error formula)
In the immediate window, Application.WorksheetFunction.Vlookup doesn't work, but Application.Vlookup does.

--
Kind regards,

Niek Otten


| Anyone who'd encountered problems when they encapsulate VLookup or
| HLookup? I'm trying to write a generalized lookup function which I can
| control the direction of lookup through an argument but when I step the code
| through, it stops execution at the line of .VLookup/ .HLookup. It does not
| halt the program flow and execute all cells that uses this formula with
| #VALUE! result.
|
| Public Function MyGeneralLookUp(Direction As Long, Key As Range, TableName
| As Range, StartPoint As Long, Match As Boolean) As String
| MyGeneralLookUp = ""
| Application.EnableEvents = False
| ' Some key to search with
| If (Not (CStr(Key.Value) = "")) Then
| ' Initialize an offset table so that data can be retrieved from the
| correct position
| Call CreateOffsetTable
| ' Calculate offset distance off StartPoint
| Offset = SeekOffset(OffsetTag)
| With Application.WorksheetFunction
| ' Enforce looking up exact key
| Match = (Not Match)
| ' Lookup value depending on direction
| Select Case Direction
| Case HDir
| FetchedResult = .VLookup(Key, TableName, Offset, Match)
| Case VDir
| FetchedResult = .HLookup(Key, TableName, Offset, Match)
| End Select
| ' No error from lookup & result is either a text or number
| If ((Not .IsError(FetchedResult)) And _
| (((.IsText(FetchedResult)) Or (.IsNumber(FetchedResult)))) _
| ) Then
| MyGeneralLookUp = FetchedResult
| End If
| End With
| End If
| Application.EnableEvents = True
| End Function
|
| Would appreciate any suggestions or pointers! Cheers!
| _______
| cLiffordiL
|
|