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

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

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

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

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

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

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

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

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

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

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

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