Reference UDF within Worksheet Formula
 

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

Reference UDF within Worksheet Formula
 

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

Reference UDF within Worksheet Formula
 

Thank you, Richard. If I have a real weakness in VBA, it's not referring to
ranges more elegantly. This looks like a good learning experience.
Evidently I cannot beat the elegance and automatic nature of just using
LOOKUP in the spreadsheet formula to reference the last row, as was
suggested in the newsgroup, but I continue to be interested in how one might
refer to a UDF within a spreadsheet formula. I will study your reply.
Regards, James
"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

Reference UDF within Worksheet Formula
 

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

Reference UDF within Worksheet Formula
 

Bob, Confused here. What if I just want to use your function to just get
the value of the last cell in column C. What is the syntax for entering the
formula into the cell? TIA, James
"Bob Phillips" wrote in message
...
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

Reference UDF within Worksheet Formula
 

In that case, you just need:

=LR(C1)

for example.

Richard


Zone wrote:
Bob, Confused here. What if I just want to use your function to just get
the value of the last cell in column C. What is the syntax for entering the
formula into the cell? TIA, James
"Bob Phillips" wrote in message
...
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

Reference UDF within Worksheet Formula
 

.... indeed.

My mod was just suggested to stop the value being recalculated (incorrectly)
if another sheet was active and a full recalculation was triggered.

--
HTH

Bob Phillips

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

"RichardSchollar" wrote in message
ups.com...
In that case, you just need:

=LR(C1)

for example.

Richard


Zone wrote:
Bob, Confused here. What if I just want to use your function to just
get
the value of the last cell in column C. What is the syntax for entering
the
formula into the cell? TIA, James
"Bob Phillips" wrote in message
...
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