Help with UDF Function

[Carim]

Hi,

Below is my code, which as a macro, works fine ...
But transposed into a Function, it does not work ...

Function PLE(Rng As Range) As Variant

Dim i, j As Integer
i = Reng.Value
j = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
PLE = Application.WorksheetFunction.Sum(Range(Cells(i, j - 2),
Cells(i, j)))

End Function

What is wrong ?
TIA

 

[Bob Phillips]

I don't think Find works in a UDF, it fails on the Find itself.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

use
OPTION EXPLICIT
and you'd not have needed to ask

i = Rng.Value

Under Tools/Options check Require Option Declaration
then Excel will add this for you

 

[Arvi Laanemets]

Hi

Search activates the cell it finds the searched value in - it means the
cursor position is changed, what means that a real change is made in
workbook. Functions in Excel aren't allowed to change anything - they only
can return a result. Use Do...While or Do...Until cycle to read data from
cells in range (not to select cells!), until the condition is true, instead.

 

[Carim]

Thanks Bob,

I was totally stuck ... !!!

Cheers

 

[Tom Ogilvy]

Just to add some information, Dave Peterson has reported that the FIND
method works in a UDF in xl2002 and later. In earlier versions, it does not
as Bob has stated.

 

[Bob Phillips]

One day, I'll upgrade <g>

Just to add some information, Dave Peterson has reported that the FIND
method works in a UDF in xl2002 and later. In earlier versions, it does not
as Bob has stated.