MS-Excel / General Formatting

Mega or Array Formulas

This method is slightly slower, but it's less likely to cause problems with more complicated code.

The code.
Enter the following code by right-clicking on your sheet tab and selecting ViewCode (pre-2007, Tools → Macro → Visual Basic Editor)
or
pressing Alt/Option-F11,
then selecting Insert → Module and pasting this code:

Sub MakeAbsoluteorRelativeSlow( )
    
    Dim RdoRange As Range, rCell 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 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 'Absolute row/Relative column
	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 'Absolute all
	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 'Relative all
	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 'Typo

	MsgBox "Change type not recognised!", vbCritical, _
	"OzGrid Business Applications"
    End Select

	 'Clear memory
	Set RdoRange = Nothing
End Sub

Click the top-right X (or press Alt-Q) to get back to Excel proper, and then save your workbook.

Again, to run the code, select the range of cells you want to change, press Alt/Option-F8, select the macro name, and click Run.

If you want to change only one formula, you could toggle through the four reference types a formula can use by selecting the cell that housing the formula, clicking in the Formula bar, then clicking inside the reference part of your formula (e.g., A1, $A$1) and pressing F4. Each press of F4 will toggle the reference type.

[Previous] [Contents]

In this tutorial:

  1. Convert Formula References from Relative to Absolute
  2. Less Complicated Formulas
  3. Mega or Array Formulas