Change Formulas to Absolute or Relative References

Add to Favorites

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





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...
Absolute and Relative Cell References in Excel
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 E...
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 for...
Vlookup Macro to Return All Matching Results from a Sheet in Excel
Macro: This Excel Macro works like a better Vlookup function because it returns ALL of the matchi...
NOT Function - Change False to True and True to False in Excel
Tutorial: Change True to False and False to True with this simple function in Excel. To do this, we ...



How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. 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.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  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.

  9. You are now ready to run the macro.


Similar Content
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...
Absolute and Relative Cell References in Excel
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 E...
Excel Forum