Convert Excel Formulas from Relative to Absolute
Category: [Excel] Demo Available
Convert Excel Formulas from Relative to Absolute
Convert Selected Excel Formulas from Absolute to Relative References or Vice Versa
Got any Excel Questions? Free Excel Help Convert Formulas from Relative to Absolute. Convert Formulas from Absolute to Relative References
The Excel macro code below can be used to convert all Excel formulas from absolute to relative and/or relative to absolute. It can also give a mix of relative row, absolute column reference, or absolute row, relative column reference. Simply select the cells that should be changed, run the code and choose the reference type you want. There are 2 Excel macros below, the first is the fastest but can cause issues with mega formulas and/or array formulas. The second is slower, but less likely to cause issues. Thanks to Andy Pope . As with any code, save you Workbook BEFORE running the code.
TIP: You can toggle through the 4 reference types a formula can use by selecting the cell housing the formula, then click in Formula Bar, then the reference part (e.g A1, $A$1 etc) and push F4. Each push of F4 will toggle the reference type.
If you are not familiar with running Excel macro code, follow the steps below:
1) Go to the Visual Basic Editor, Tools>Macro>Visual Basic Editor (Alt+F11).
2) Insert a standard Module, Insert>Module.
3) Copy the code and paste directly in the Module just added.
4) Go back to Excel proper, click the top right X, or push Alt+Q and save!
5) Save you Workbook. Select your range of cells to run the code on.
6) Run the macro, go to Tools>Macro>Macros (Alt+F8) select the macro name and click "Run".
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
Sub MakeAbsoluteorRelativeSlow() 'Written for www.ozgrid.com 'By Andy Pope 'www.andypope.info/ 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
See also:
| Find Method to Find Dates |
| Excel VBA Find Method |
| Force Text to Upper Case/Proper Case |
| Used to Loop Through a Collection or Array |
| For Loop |
| For Loop Step |
See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.