I have a problem using the find method of range object. It works fine if I
use into a Sub procedure; but fails (i.e. return nothing all time) if I use
it into a Function.
Image I have the following excel column (A1:A4):
10
20
30
40

the following sub is ok (the messagebox show A2):
Sub FindTest()
Dim R as Range
Dim C as Range
Set R = ActiveSheet.Range("A:A")
Set C = R.Find(20,,xlValues)
MsgBox C.Address
End

but the following function desn't work (C is always Nothing):
Function FindTestFn() as Variant
Dim R as Range
Dim C as Range
Set R = ActiveSheet.Range("A:A")
Set C = R.Find(20,,xlValues)
FindTestFn = C.Address
End

Thanks in advance...

Eros Pedrini wrote:
I have a problem using the find method of range object. It works fine if I
use into a Sub procedure; but fails (i.e. return nothing all time) if I use
it into a Function.

The Function works if it is called from a Sub Procedure; it doesn't if
it is called from a Worksheet. That's just the way it is in Excel.

Alan Beban

Dave Peterson says FIND does work in a User Defined Function (UDF) used in a
worksheet in xl2002 and later. Just for information.

--
Regards,
Tom Ogilvy

"Alan Beban" wrote in message
...
Eros Pedrini wrote:
I have a problem using the find method of range object. It works fine if
I
use into a Sub procedure; but fails (i.e. return nothing all time) if I
use
it into a Function.

The Function works if it is called from a Sub Procedure; it doesn't if
it is called from a Worksheet. That's just the way it is in Excel.

Alan Beban

Tom Ogilvy wrote:
Dave Peterson says FIND does work in a User Defined Function (UDF) used in a
worksheet in xl2002 and later. Just for information.

Thanks; that by itself is almost enough reason to upgrade.

Alan Beban

Thanks... another question... there is a workaround? Check all cells with an
each for is very expensive in time... something know another way?

Thanks again

Eros Pedrini




"Alan Beban" wrote in message
...

The Function works if it is called from a Sub Procedure; it doesn't if
it is called from a Worksheet. That's just the way it is in Excel.

Alan Beban

for a single column or single row, use application.Match

Function FindTestFn() As Variant
Dim R As Range
Dim res As Variant
Dim R1 As Range
Application.Volatile
Set R1 = Application.Caller
Set R = R1.Parent.Range("A:A")
res = Application.Match(20, R, 0)
If Not IsError(res) Then
FindTestFn = R(res).Address
Else
FindTextFn = CVErr(xlErrNA)
End If
End Function




--
Regards,
Tom Ogilvy

"Eros Pedrini" wrote in message
...
Thanks... another question... there is a workaround? Check all cells with
an
each for is very expensive in time... something know another way?

Thanks again

Eros Pedrini




"Alan Beban" wrote in message
...

The Function works if it is called from a Sub Procedure; it doesn't if
it is called from a Worksheet. That's just the way it is in Excel.

Alan Beban