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.
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