MS-Excel / General Formatting

---

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.