Cell location
 

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

Thanks

--
ehnt
-----------------------------------------------------------------------
ehntd's Profile: http://www.excelforum.com/member.php...fo&userid=1586
View this thread: http://www.excelforum.com/showthread.php?threadid=38980

Cell location
 

"ehntd" wrote in message
...
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

Cell location
 

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" wrote in message
...

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

Cell location
 

That worked great! Thanks

--
ehnt
-----------------------------------------------------------------------
ehntd's Profile: http://www.excelforum.com/member.php...fo&userid=1586
View this thread: http://www.excelforum.com/showthread.php?threadid=38980

Cell location
 

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

Cell location
 

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