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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>