Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Make all absolute (2002)

    I need to select a range of 3,600 cells that have formula's looking at another sheet, and make all of the formula's absolute. Is there an easy way?

  2. #2
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make all absolute (2002)

    Thanks Jerry, Can I check 2 things.

    1. Am I right to assume that my range should be selected prior to running the code? added later..... <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> <img src=/S/rtfm.gif border=0 alt=rtfm width=24 height=23>
    2. Some of the cells are already partially absolute, will this make a difference?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Make all absolute (2002)

    <P ID="edit" class=small>(Edited by sdckapr on 25-Aug-08 18:34. Edited to wrap the long line)</P>Hi Nathan

    Use this piece of code:

    <pre>Sub makeAbs()
    Dim x As Range, y As String, z As String
    For Each x In Selection.SpecialCells(3)
    y = x.Formula
    z = Application.ConvertFormula(Formula:=y, _
    fromReferenceStyle:=xlA1, toReferenceStyle:=xlA1, toAbsolute:=xlAbsolute)
    x.Formula = z
    Next
    End Sub

    </pre>


    Highlight the range and then run sub
    Jerry

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Make all absolute (2002)

    <P ID="edit" class=small>(Edited by Jezza on 25-Aug-08 23:36. To change response to better English)</P>Hi Nathan

    1) Yes
    2) Yes it will make a difference, that was not in the original question <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Jerry

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make all absolute (2002)

    Worked perfect, Thanks Jerry.

Posting Permissions

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