Cell location

Hi, I am trying to build a function that returns the cell on which it is in. For example, if I place it on A1, then it will return A1. After that I want to copy and paste into the other cells and have A2, A3 and so on. The problem is that I tried to do it with activecell and that does not work, since when I copy and paste the activecell is still A1. Do you have any ideas?

Thanks!

"ehntd" <[email protected]> wrote in message
news:[email protected]...
> Hi, I am trying to build a function that returns the cell on which it is
> in. For example, if I place it on A1, then it will return A1. After
> that I want to copy and paste into the other cells and have A2, A3 and
> so on. The problem is that I tried to do it with activecell and that
> does not work, since when I copy and paste the activecell is still A1.
> Do you have any ideas?

This will work for columns A through Z and for as many rows as you want:

=CHAR(64+COLUMN()) & ROW()

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

Here is a UDF

Function WhereAmI()
WhereAmI = Application.Caller.Address(False, False)
End Function


Call with =WhereAmI()

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ehntd" <[email protected]> wrote in message
news:[email protected]...
>
> Hi, I am trying to build a function that returns the cell on which it is
> in. For example, if I place it on A1, then it will return A1. After
> that I want to copy and paste into the other cells and have A2, A3 and
> so on. The problem is that I tried to do it with activecell and that
> does not work, since when I copy and paste the activecell is still A1.
> Do you have any ideas?
>
> Thanks!
>
>
> --
> ehntd
> ------------------------------------------------------------------------
> ehntd's Profile:
http://www.excelforum.com/member.php...o&userid=15865
> View this thread: http://www.excelforum.com/showthread...hreadid=389809
>

That worked great! Thanks!

Or just a worksheet function.

In A1:
=cell("address",a1)
or
=substitute(cell("address",a1),"$","")
(if you really don't want the $ signs.)

ehntd wrote:
>
> Hi, I am trying to build a function that returns the cell on which it is
> in. For example, if I place it on A1, then it will return A1. After
> that I want to copy and paste into the other cells and have A2, A3 and
> so on. The problem is that I tried to do it with activecell and that
> does not work, since when I copy and paste the activecell is still A1.
> Do you have any ideas?
>
> Thanks!
>
> --
> ehntd
> ------------------------------------------------------------------------
> ehntd's Profile: http://www.excelforum.com/member.php...o&userid=15865
> View this thread: http://www.excelforum.com/showthread...hreadid=389809

--

Dave Peterson

WITHOUT THE USE OF A MACRO . . .

YOU CAN USE ADDRESS WITH A ROW AND COLUMN FUNCTION IMBEDDED.

=ADDRESS(ROW(),COLUMN(),4)

PASTE THAT FORMULA IN ANY CELL AND IT WILL GIVE YOU THE CELL ADDRESS.




"ehntd" wrote:

>
> Hi, I am trying to build a function that returns the cell on which it is
> in. For example, if I place it on A1, then it will return A1. After
> that I want to copy and paste into the other cells and have A2, A3 and
> so on. The problem is that I tried to do it with activecell and that
> does not work, since when I copy and paste the activecell is still A1.
> Do you have any ideas?
>
> Thanks!
>
>
> --
> ehntd
> ------------------------------------------------------------------------
> ehntd's Profile: http://www.excelforum.com/member.php...o&userid=15865
> View this thread: http://www.excelforum.com/showthread...hreadid=389809
>
>