Change Formulas to Absolute or Relative References

This macro will change the formulas in cells to absolute or relative. It allows you to change the rows or the columns or both to absolute or relative references. This macro works on a selection of cells. This means that you can select only the cells you want to change and then run the macro on them. Alternatively, you can not select a cell with a formula, run the macro, and then the entire worksheet and workbook's formulas with have the type of references you selected.

When the macro is run, you select, from a pop-up box, what kind of references you want the formulas to be. This macro will save you a lot of time if you need to change a large number of formulas or if you just need to change a few quickly.

Where to install the macro:  Module

Change Formulas to Absolute or Relative References

Sub Change_Cells_To_Absolute_Relative()
    Dim RdoRange As Range, rCell As Range
    Dim i As Integer
    Dim Reply As String
    Reply = InputBox("Change formulas to?" & Chr(13) & Chr(13) _
     & "Relative row/Absolute column = 1" & Chr(13) _
     & "Absolute row/Relative column = 2" & Chr(13) _
     & "Absolute all = 3" & Chr(13) _
     & "Relative all = 4")
    If Reply = "" Then Exit Sub
    On Error Resume Next
    Set RdoRange = Selection.SpecialCells(Type:=xlFormulas)
    Select Case Reply
    Case 1
        For Each rCell In RdoRange
            If rCell.HasArray Then
                If Len(rCell.FormulaArray) < 255 Then
                    rCell.FormulaArray = _
                     Application.ConvertFormula _
                     (Formula:=rCell.FormulaArray, _
                     FromReferenceStyle:=xlA1, _
                     ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)
                End If
            Else
                If Len(rCell.Formula) < 255 Then
                    rCell.Formula = _
                     Application.ConvertFormula _
                     (Formula:=rCell.Formula, _
                     FromReferenceStyle:=xlA1, _
                     ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)
                End If
            End If
        Next rCell  
    Case 2
        For Each rCell In RdoRange
            If rCell.HasArray Then
                If Len(rCell.FormulaArray) < 255 Then
                    rCell.FormulaArray = _
                     Application.ConvertFormula _
                     (Formula:=rCell.FormulaArray, _
                     FromReferenceStyle:=xlA1, _
                     ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)
                End If
            Else
                If Len(rCell.Formula) < 255 Then
                    rCell.Formula = _
                     Application.ConvertFormula _
                     (Formula:=rCell.Formula, _
                     FromReferenceStyle:=xlA1, _
                     ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)
                End If
            End If
        Next rCell
    Case 3
        For Each rCell In RdoRange
            If rCell.HasArray Then
                If Len(rCell.FormulaArray) < 255 Then
                    rCell.FormulaArray = _
                     Application.ConvertFormula _
                     (Formula:=rCell.FormulaArray, _
                     FromReferenceStyle:=xlA1, _
                     ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
                End If
            Else
                If Len(rCell.Formula) < 255 Then
                    rCell.Formula = _
                     Application.ConvertFormula _
                     (Formula:=rCell.Formula, _
                     FromReferenceStyle:=xlA1, _
                     ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
                End If
            End If
        Next rCell
    Case 4
        For Each rCell In RdoRange
            If rCell.HasArray Then
                If Len(rCell.FormulaArray) < 255 Then
                    rCell.FormulaArray = _
                     Application.ConvertFormula _
                     (Formula:=rCell.FormulaArray, _
                     FromReferenceStyle:=xlA1, _
                     ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)
                End If
            Else
                If Len(rCell.Formula) < 255 Then
                    rCell.Formula = _
                     Application.ConvertFormula _
                     (Formula:=rCell.Formula, _
                     FromReferenceStyle:=xlA1, _
                     ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)
                End If
            End If
        Next rCell
    Case Else
        MsgBox "Invalid Number Entered!", vbCritical
    End Select
    Set RdoRange = Nothing
End Sub

---