Change Multiple Formula Range References Between Absolute/Relative

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!

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.

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.

Re: Change Multiple Formula Range References Between Absolute/Relative

You can use the Edit Menu Find "$" replace with "" to go absolute to relative.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]This will cycle all the formulas in the selected cells through the 4 Absolute/Relative combinations.

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

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

Re: Change Multiple Formula Range References Between Absolute/Relative

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

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.