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

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)


"Carim" wrote in message
oups.com...
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

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


"Carim" wrote:

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

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.

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Carim" wrote in message
oups.com...
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

Thanks Bob,

I was totally stuck ... !!!

Cheers

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.

--
Regards,
Tom Ogilvy


"Carim" wrote in message
oups.com...
Thanks Bob,

I was totally stuck ... !!!

Cheers

One day, I'll upgrade <g


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

--
Regards,
Tom Ogilvy


"Carim" wrote in message
oups.com...
Thanks Bob,

I was totally stuck ... !!!

Cheers