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

A function used in a worksheet can only return a value to the cell in which
it is used. It can not alter the excel environment, such as changing values
in other cells or formatting in any cell as examples.

--
Regards,
Tom Ogilvy
"Zsola" wrote in message
...
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

Thank you for your answer!

I note, that the function works as a worksheet function in
this form, but if I remove the " ' " (without this it
won't work).
The main questonaire is, how can I use this function with
changed values in the range given as parameter.

Thanks in advance.


-----Original Message-----
A function used in a worksheet can only return a value to
the cell in which
it is used. It can not alter the excel environment, such
as changing values
in other cells or formatting in any cell as examples.

--
Regards,
Tom Ogilvy
"Zsola" wrote in message
...
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



.

I believe, that within your function, you would have to put the values of
the rng in an array and use the array as the second argument to Vlookup. In
xl2000 and earlier, I believe the number of cells in the range will not be
able to exceed 5461.

--
Regards,
Tom Ogilvy

"Zsola" wrote in message
...
Thank you for your answer!

I note, that the function works as a worksheet function in
this form, but if I remove the " ' " (without this it
won't work).
The main questonaire is, how can I use this function with
changed values in the range given as parameter.

Thanks in advance.


-----Original Message-----
A function used in a worksheet can only return a value to
the cell in which
it is used. It can not alter the excel environment, such
as changing values
in other cells or formatting in any cell as examples.

--
Regards,
Tom Ogilvy
"Zsola" wrote in message
...
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



.

Thank you Tom!

I'm not an expert... and how about putting range values
into arrays?

Thanks



-----Original Message-----
I believe, that within your function, you would have to
put the values of
the rng in an array and use the array as the second
argument to Vlookup. In
xl2000 and earlier, I believe the number of cells in the
range will not be
able to exceed 5461.

--
Regards,
Tom Ogilvy

"Zsola" wrote in
message
...
Thank you for your answer!

I note, that the function works as a worksheet
function in
this form, but if I remove the " ' " (without this it
won't work).
The main questonaire is, how can I use this function
with
changed values in the range given as parameter.

Thanks in advance.


-----Original Message-----
A function used in a worksheet can only return a
value to
the cell in which
it is used. It can not alter the excel environment,
such
as changing values
in other cells or formatting in any cell as examples.

--
Regards,
Tom Ogilvy
"Zsola" wrote in message
...
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



.



.

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

Maybe something like this:

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

Dim myArr As Variant
myArr = rng.Value
myArr(1, 1) = 2
fkeres1 = Application.VLookup(cella, myArr, i, logikai)

End Function



Zsola wrote:

Thank you Tom!

I'm not an expert... and how about putting range values
into arrays?

Thanks

-----Original Message-----
I believe, that within your function, you would have to
put the values of
the rng in an array and use the array as the second
argument to Vlookup. In
xl2000 and earlier, I believe the number of cells in the
range will not be
able to exceed 5461.

--
Regards,
Tom Ogilvy

"Zsola" wrote in
message
...
Thank you for your answer!

I note, that the function works as a worksheet
function in
this form, but if I remove the " ' " (without this it
won't work).
The main questonaire is, how can I use this function
with
changed values in the range given as parameter.

Thanks in advance.


-----Original Message-----
A function used in a worksheet can only return a
value to
the cell in which
it is used. It can not alter the excel environment,
such
as changing values
in other cells or formatting in any cell as examples.

--
Regards,
Tom Ogilvy
"Zsola" wrote in message
...
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