Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I reference a worksheet name through a formula? | Excel Worksheet Functions | |||
Formula to reference another worksheet | Excel Discussion (Misc queries) | |||
Reference a Worksheet Tab in Formula | Excel Worksheet Functions | |||
two worksheet reference formula help | Excel Worksheet Functions | |||
Worksheet name / reference as a formula? | Excel Discussion (Misc queries) |