Custom Function: Detecting the cell the function is used in

[g-boy]

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

 

[Robin Hammond]

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]

Thank you! That is *exactly* what I was looking for!