Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Fixing cells ($) for multiple cells

  1. #1
    New Member
    Join Date
    Jun 2009
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Fixing cells ($) for multiple cells

    I know I can put a $ sign for all references in a formula for a cell by pressing F4, but is there a quick way for doing this for multiple cells (e.g. a column) or do I have to go through all the cells individually?

    e.g.
    =A1
    =A2
    =A3

    to

    =$A$1
    =$A$2
    =$A$3

    Thanks

  2. #2
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Fixing cells ($) for multiple cells

    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    New Member
    Join Date
    Jun 2009
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fixing cells ($) for multiple cells

    Thanks VoG

    It seems like you have to buy that (for a proper legal version for a company anyway). I suppose I could just write a macro for a large amount of cells.

    Cheers

  4. #4
    New Member
    Join Date
    Jun 2009
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fixing cells ($) for multiple cells

    You can use Edit-> Replace to a limited extent.

    Just select the range of cells, do something like Replace "A" with "$A$".

  5. #5
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,052
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fixing cells ($) for multiple cells

    Quote Originally Posted by sputnik17 View Post
    Thanks VoG

    It seems like you have to buy that (for a proper legal version for a company anyway). I suppose I could just write a macro for a large amount of cells.

    Cheers
    yes you can, look at the convertformula function...

    Here's a simple example

    Range("A1").Formula = Application.ConvertFormula(Range("A1").Formula, xlA1, xlA1, 1)

    The 4th argument is the absolute style

    1 = col & row abs
    2 = row abs, col relative
    3 = col relative, row abs
    4 = row & col relative

    Unfortunately, it converts the ENTIRE formula's references. so you can't mix it up like

    =$A$1+B1

    you couldn't have it convert to that, both cell refs would have the same absolute style..
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com