EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

Convert Excel Formulas from Relative to Absolute

 

Convert Selected Excel Formulas from Absolute to Relative References or Vice Versa

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 Instant Buy/Download Includes Advanced Excel and 2 Excel VBA Courses.

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

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.


Instant Download and Money Back Guarantee on Most Software

EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates