Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Change Formulas to Absolute or Relative References
This macro will change the formulas in cells to absolute or relative. It allows you to change the rows or the columns or both to absolute or relative references. This macro works on a selection of cells. This means that you can select only the cells you want to change and then run the macro on them. Alternatively, you can not select a cell with a formula, run the macro, and then the entire worksheet and workbook's formulas with have the type of references you selected.
When the macro is run, you select, from a pop-up box, what kind of references you want the formulas to be. This macro will save you a lot of time if you need to change a large number of formulas or if you just need to change a few quickly.
Where to install the macro: Module
Change Formulas to Absolute or Relative References
Sub Change_Cells_To_Absolute_Relative()
Dim RdoRange As Range, rCell As Range
Dim i As Integer
Dim Reply As String
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")
If Reply = "" Then Exit Sub
On Error Resume Next
Set RdoRange = Selection.SpecialCells(Type:=xlFormulas)
Select Case Reply
Case 1
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
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
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
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
MsgBox "Invalid Number Entered!", vbCritical
End Select
Set RdoRange = Nothing
End Sub
Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Similar Content on TeachExcel
Top Excel Keyboard Shortcuts to Increase Productivity
Tutorial: I'll show you the top keyboard shortcuts for Excel that are sure to increase your producti...
Tutorial: I'll show you the top keyboard shortcuts for Excel that are sure to increase your producti...
Absolute and Relative Cell References in Excel
Tutorial: In this tutorial I am going to cover the difference between Absolute and Relative Cell Ref...
Tutorial: In this tutorial I am going to cover the difference between Absolute and Relative Cell Ref...
Apply Conditional Formatting to Multiple Cells with a Single Formula
Tutorial: How to use a single formula to apply conditional formatting to multiple cells at once in ...
Tutorial: How to use a single formula to apply conditional formatting to multiple cells at once in ...
Formula to Count Occurrences of a Word in a Cell or Range in Excel
Tutorial: Formula to count how many times a word appears in a single cell or an entire range in Exce...
Tutorial: Formula to count how many times a word appears in a single cell or an entire range in Exce...
Formula to Get the Last Value from a List in Excel
Tutorial: Formulas that you can use to get the value of the last non-empty cell in a range in Excel...
Tutorial: Formulas that you can use to get the value of the last non-empty cell in a range in Excel...
Formula to Delete the First or Last Word from a Cell in Excel
Tutorial: Excel formula to delete the first or last word from a cell. You can copy and paste the fo...
Tutorial: Excel formula to delete the first or last word from a cell. You can copy and paste the fo...
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.