Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function: Detecting the cell the function is used in
Suppose you have a column that contains groups of values separated by
subtotals. So, for example: Col_Header 12 15 =sum(...) 11 3 14 =sum(...) and so on. You can't just copy and past the same SUM expression into each of your sub-total rows, because each group does not contain the same number of rows. I would like to create a custom worksheet function (let's call it the SUMUP function)that I can place in a cell, where it will look *up* the column and sum all of the values it encounters UNTIL it encounters a cell that contains either text or another SUMUP function. That way, I can paste the *same* function anywhere that I want a sub-total, without having to worry about how many rows are being included in the subtotal. From a programming standpoint, this is straightforward, with one hitch: how do I "detect" the cell that the function is actually being used in? That is, if I type the function in cell G13, I need to be able to have a variable within the function that "knows" that the function is located in G13 (so it can start the process of summing the values from G12 on up the column). This seems like it should be a straight-forward thing, I just haven't been able to find it. Thanks in advance for any help or suggestions... -G |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function: Detecting the cell the function is used in
G,
You are looking for Application.Caller. put this formula in a cell =CellAddress() 'put this in a standard module Public Function CellAddress() As String CellAddress = Application.Caller.Address End Function Robin Hammond www.enhanceddatasystems.com "g-boy" wrote in message ... Suppose you have a column that contains groups of values separated by subtotals. So, for example: Col_Header 12 15 =sum(...) 11 3 14 =sum(...) and so on. You can't just copy and past the same SUM expression into each of your sub-total rows, because each group does not contain the same number of rows. I would like to create a custom worksheet function (let's call it the SUMUP function)that I can place in a cell, where it will look *up* the column and sum all of the values it encounters UNTIL it encounters a cell that contains either text or another SUMUP function. That way, I can paste the *same* function anywhere that I want a sub-total, without having to worry about how many rows are being included in the subtotal. From a programming standpoint, this is straightforward, with one hitch: how do I "detect" the cell that the function is actually being used in? That is, if I type the function in cell G13, I need to be able to have a variable within the function that "knows" that the function is located in G13 (so it can start the process of summing the values from G12 on up the column). This seems like it should be a straight-forward thing, I just haven't been able to find it. Thanks in advance for any help or suggestions... -G |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function: Detecting the cell the function is used in
Thank you! That is *exactly* what I was looking for!
"Robin Hammond" wrote: G, You are looking for Application.Caller. put this formula in a cell =CellAddress() 'put this in a standard module Public Function CellAddress() As String CellAddress = Application.Caller.Address End Function Robin Hammond www.enhanceddatasystems.com "g-boy" wrote in message ... Suppose you have a column that contains groups of values separated by subtotals. So, for example: Col_Header 12 15 =sum(...) 11 3 14 =sum(...) and so on. You can't just copy and past the same SUM expression into each of your sub-total rows, because each group does not contain the same number of rows. I would like to create a custom worksheet function (let's call it the SUMUP function)that I can place in a cell, where it will look *up* the column and sum all of the values it encounters UNTIL it encounters a cell that contains either text or another SUMUP function. That way, I can paste the *same* function anywhere that I want a sub-total, without having to worry about how many rows are being included in the subtotal. From a programming standpoint, this is straightforward, with one hitch: how do I "detect" the cell that the function is actually being used in? That is, if I type the function in cell G13, I need to be able to have a variable within the function that "knows" that the function is located in G13 (so it can start the process of summing the values from G12 on up the column). This seems like it should be a straight-forward thing, I just haven't been able to find it. Thanks in advance for any help or suggestions... -G |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing active cell works in a sub but not in a custom function | Excel Worksheet Functions | |||
referencing active cell works in a sub but not in a custom function | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Refer to selected cell in custom function | Excel Programming | |||
Adding a custom function to the default excel function list | Excel Programming |