Announcement

Collapse
No announcement yet.

Change Multiple Formula Range References Between Absolute/Relative

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Change Multiple Formula Range References Between Absolute/Relative



    Experts:

    Is there a way to absolute reference multiple cells at one time? Thanks.

  • #2
    Re: Absolute Reference Multiple Cells At One Time

    depends how you want to do it is it for an intersect or an array range or...? we need more information!
    Regards,
    Simon

    In the unlikely event you don't get an answer here try Microsoft Office Help for FREE!
    Please take the time to read these rules before posting!

    Comment


    • #3
      Re: Absolute Reference Multiple Cells At One Time

      sorry for not being clear.

      basically, I want to select a group of cells. then toggle F4 and all the cell references within the formulas in my selection will toggle simultaneously.

      hope that is clearer.

      Comment


      • #4
        Re: Absolute Reference Multiple Cells At One Time

        In a word, unless you're talking about an arrray formula: No.

        Clarification: If the same formula is going in all the cells, then you can select the range, use F4 to toggle references, and then use Ctrl+Enter to group-enter the formula in all the cells at once.
        Entia non sunt multiplicanda sine necessitate.

        Comment


        • #5
          Re: Absolute Reference Multiple Cells At One Time

          Convert Formulas from Relative to Absolute & Absolute to Relative.

          Convert Excel Formulas from...

          A few possible answers above too.

          Comment


          • #6
            Re: Change Multiple Formula Range References Between Absolute/Relative

            You can use the Edit Menu Find "$" replace with "" to go absolute to relative.
            Auto Merged Post Until 24 Hrs Passes;

            This will cycle all the formulas in the selected cells through the 4 Absolute/Relative combinations.
            Code:
            Sub CycleAbsRel()
            Dim inRange as Range, oneCell As Range
            Static absRelMode As Long
            absRelMode = (absRelMode Mod 4) + 1
            
            Set inRange = Selection.SpecialCells(xlCellTypeFormulas)
            
            If Not (inRange Is Nothing) Then
                For Each oneCell In inRange
                    With oneCell
                    .FormulaR1C1 = Application.ConvertFormula(.FormulaR1C1, xlR1C1, xlR1C1, absRelMode, oneCell)
                    End With
                Next oneCell
            End If
            End Sub
            Last edited by mikerickson; March 16th, 2008, 12:32. Reason: Auto Merged Doublepost

            Comment


            • #7
              Re: Change Multiple Formula Range References Between Absolute/Relative

              Works great for =D1 to =$D$1 but throws up #VALUE! if my forumla is =Sheet1!A1

              Comment


              • #8


                Re: Change Multiple Formula Range References Between Absolute/Relative

                I cannot reproduce this. I had only one cell with that formula, and then ran:

                Code:
                Sub CycleAbsRel()
                    Dim inRange As Range, oneCell As Range
                    Static absRelMode As Long
                    absRelMode = (absRelMode Mod 4) + 1
                
                    Set inRange = Cells.SpecialCells(xlCellTypeFormulas)
                
                    For Each oneCell In inRange
                        With oneCell
                            .FormulaR1C1 = Application.ConvertFormula(.FormulaR1C1, xlR1C1, xlR1C1, absRelMode, oneCell)
                        End With
                    Next
                End Sub
                This worked fine.
                Regards,

                Wigi

                Excel MVP 2011-2014

                For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

                -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

                Comment

                Working...
                X