Less Complicated Formulas
Using the following code is the fastest method, but it can cause problems when used with more complicated formulas.
As with any code, always save your workbook before running the code.
The code. Right-click on your sheet tab and select ViewCode (pre-2007, Tools → Macro → Visual Basic Editor) or press Alt/Option-F11.
To insert the
following code into a standard module, select Insert → Module:
Sub MakeAbsoluteorRelativeFast( ) 'Written by OzGrid Business Applications 'www.ozgrid.com Dim RdoRange As Range Dim i As Integer Dim Reply As String 'Ask whether Relative or Absolute 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", "OzGrid Business Applications") 'They cancelled If Reply = "" Then Exit Sub On Error Resume Next 'Set Range variable to formula cells only Set RdoRange = Selection.SpecialCells(Type:=xlFormulas) 'determine the change type Select Case Reply Case 1 'Relative row/Absolute column For i = 1 To RdoRange.Areas.Count RdoRange.Areas(i).Formula = _ Application.ConvertFormula _ (Formula:=RdoRange.Areas(i).Formula, _ FromReferenceStyle:=xlA1, _ ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn) Next i Case 2 'Absolute row/Relative column For i = 1 To RdoRange.Areas.Count RdoRange.Areas(i).Formula = _ Application.ConvertFormula _ (Formula:=RdoRange.Areas(i).Formula, _ FromReferenceStyle:=xlA1, _ ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn) Next i Case 3 'Absolute all For i = 1 To RdoRange.Areas.Count RdoRange.Areas(i).Formula = _ Application.ConvertFormula _ (Formula:=RdoRange.Areas(i).Formula, _ FromReferenceStyle:=xlA1, _ ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute) Next i Case 4 'Relative all For i = 1 To RdoRange.Areas.Count RdoRange.Areas(i).Formula = _ Application.ConvertFormula _ (Formula:=RdoRange.Areas(i).Formula, _ FromReferenceStyle:=xlA1, _ ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative) Next i Case Else 'Typo MsgBox "Change type not recognised!", vbCritical, _ "OzGrid Business Applications" End Select 'Clear memory Set RdoRange = Nothing End Sub
Now, click the top-right X (or press Alt-Q) to get back to Excel proper, and then save your workbook.
To run the code, select the range of cells you want to change, press Alt/Option-F8, select the macro name, and click Run. A dialog will pop up, giving you four options.
Depending on the result you are looking for, you will make a selection of 1, 2, 3, or 4, then click OK and your formula will be converted to comply with your selection.
There is no "Undo" option after you have run this macro.