This might be too specific if you really were just showing an example:

Option Explicit
Function fkeres1(ByVal cella, rng As Range, _
Optional i As Long = 2, _
Optional logikai As Boolean = False)

If cella = 2 Then
fkeres1 = rng(1).Offset(0, i - 1).Value
Else
fkeres1 = Application.VLookup(cella, rng, i, logikai)
End If

End Function

(Application.vlookup handles no match (#n/a's) nicer than
worksheetfunction.vlookup.)






Zsola wrote:

Hi All,

I'm working in excel97 VBA.
My aim is to "override" the original VLookup worksheet
function with my own function declarated below. In this
function I use "rng" and rng1 as Range variable, and I
would like to change the values in the range's first
column before working with it. (That's and others why
using new variable rng1 as Range.)

I can't explain, why doesn't it work at all if only I rem
the " 'rng1.Range("a1").Value = 2 " line.

Function fkeres1(ByVal cella, rng As Range, Optional i As
Long = 2, Optional logikai As Boolean = False)
Dim rng1 As Range

Set rng1 = rng
'rng1.Range("a1").Value = 2

fkeres1 = WorksheetFunction.VLookup(cella, rng, i, logikai)
End Function

--

Dave Peterson