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.comDim RdoRange As RangeDim i As IntegerDim Reply As String'Ask whether Relative or AbsoluteReply = 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 cancelledIf Reply = "" Then Exit SubOn Error Resume Next'Set Range variable to formula cells onlySet RdoRange = Selection.SpecialCells(Type:=xlFormulas)'determine the change typeSelect Case ReplyCase 1 'Relative row/Absolute columnFor i = 1 To RdoRange.Areas.CountRdoRange.Areas(i).Formula = _Application.ConvertFormula _(Formula:=RdoRange.Areas(i).Formula, _FromReferenceStyle:=xlA1, _ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)Next iCase 2 'Absolute row/Relative columnFor i = 1 To RdoRange.Areas.CountRdoRange.Areas(i).Formula = _Application.ConvertFormula _(Formula:=RdoRange.Areas(i).Formula, _FromReferenceStyle:=xlA1, _ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)Next iCase 3 'Absolute allFor i = 1 To RdoRange.Areas.CountRdoRange.Areas(i).Formula = _Application.ConvertFormula _(Formula:=RdoRange.Areas(i).Formula, _FromReferenceStyle:=xlA1, _ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)Next iCase 4 'Relative allFor i = 1 To RdoRange.Areas.CountRdoRange.Areas(i).Formula = _Application.ConvertFormula _(Formula:=RdoRange.Areas(i).Formula, _FromReferenceStyle:=xlA1, _ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)Next iCase Else 'TypoMsgBox "Change type not recognised!", vbCritical, _"OzGrid Business Applications"End Select'Clear memorySet RdoRange = NothingEnd 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.