range.find method called into a VBA function (problem)

[Guest]

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...

 

[Alan Beban]

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]

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

 

[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.

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

Alan Beban

 

[Eros Pedrini]

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

 

[Tom Ogilvy]

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