+ Reply to Thread
Results 1 to 6 of 6

Cell location

  1. #1
    Registered User
    Join Date
    10-29-2004
    Posts
    8

    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!

  2. #2
    Rob Bovey
    Guest

    Re: Cell location

    "ehntd" <ehntd.1spwyb_1122282332.107@excelforum-nospam.com> wrote in message
    news:ehntd.1spwyb_1122282332.107@excelforum-nospam.com...
    > 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



  3. #3
    Bob Phillips
    Guest

    Re: 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" <ehntd.1spwyb_1122282332.107@excelforum-nospam.com> wrote in message
    news:ehntd.1spwyb_1122282332.107@excelforum-nospam.com...
    >
    > 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
    >




  4. #4
    Registered User
    Join Date
    10-29-2004
    Posts
    8
    That worked great! Thanks!

  5. #5
    Dave Peterson
    Guest

    Re: 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

  6. #6
    gbor
    Guest

    RE: 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
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1