Suggested slight mod

Function LR(r As Range) As Range
With Application.Caller.Parent
Set LR = .Cells(.Rows.Count, r.Column).End(xlUp)
End With
End Function


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RichardSchollar" wrote in message
oups.com...
Hi James

I think you might want a more flexible formula such as:

Function LR(r as range) as range
Set LR=cells(application.rows.count,r.column).end(xlup )
End Function

which would give a cell ref of the last cell containing a value in the
referenced column.

But back to your original question, yes you could use it, but you'd
need to use a helper function to like Index:

=((INDEX($C:$C,LR())/L1)*B7)-F2

for example.

Happy New Year to you too :-)))

Richard


Zone wrote:
I recently tried to answer someone who wanted the worksheet formula
= ((C9/L1)*B7)-F2 automatically adjusted so that C9 referred instead to
the
last cell in column C. A nice solution was provided by someone else.
But
it left me wondering. What if I created the UDF

Function LR()
LR=cells(65536,"c").end(xlup).row
End Function

to provide the last row (or address)? Is it possible to reference this
UDF
within the spreadsheet formula? Best wishes for the new year, James