Excel Macro VBA - Change Formulas to Absolute or Relative References - Free Excel Macros VBA

Search TeachExcel.com

Video Tutorials

Macros

Excel Tutorials For Macros

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

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.
1. Go to the menu at the top of the window and click Insert > Module
2. Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
3. Go to Step 8.

If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
1. Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
2. Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
3. A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
4. Go to Step 8.

If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
1. Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
2. Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
3. A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
4. Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
5. Go 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.

Similar Helpful Excel Resources

Problem Converting Formulas To Relative/absolute References With Formulas Referencing Other Sheets - Excel

View Content

I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly. Is there anyway to get around this?

Macro To Change Absolute To Relative Cell References - Excel

View Content

Hello all,

I have found the following macro online which is supposed to allow me to select multiple cells and have the formulas within them change from relative to absolute references. Unfortunately the macro does not seem to work for me. Not being a VBA expert, can somebody please look at the following code and see if they can spot the error? Very much appreciated.

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

Change From Absolute To Relative References With Excel Macro. - Excel

View Content

Hello Bos ? anyone can help me, please..
How to change from absolute references to relative references.

Example :
ws.Range("G" & NextRow).Formula = "=" & Range("H" & NextRow).Address & "+" & ws.Range("I" & NextRow).Address
This code return the absolute references---> =$H$365+$I$365
, and i want change to relative references, like this ---> =H365+I365

Thank's b4

Change Relative To Absolute References In A Range Without Macro - Excel

View Content

I have a range of cells, with vey simple formula like
=b1
=b6
=c9
=d80

This is only one example column but there are other columns beside it. I would like to make the rows absolute in this type of range without going to every cell and entering F4 multiple times and also without using a macro.

Is there a way I can highlight the range and make it all absolute or give it mixed reference.

I know one can highlight range and enter formula in all of them at once by pressing Ctrl and then hitting enter, is there a substitute like that to change cell reference in a range

Change All Of The Relative References To Absolute In An Entire Worksheet - Excel

View Content

Hi there...

Hoping someone can help me out.

I have a large selection of cells that have lengthy formulas and I am looking for a way to select the entire group of cells and change all of the relative references within the formulas to absolute references.

Ultimately, I want to copy this section of cells below the original cells on the same worksheet and not change the formulas (I will do use the Replace function to change 1 variable after copying). I can't use "replace" to change the formulas, because there are so many different variables, it would take me all day.

Thanks! Liz

Change An Number Of Cell References From Relative To Absolute - Excel

View Content

I would like to change a range of cell references from relative to absolute. I would like to perform this change at one go (for eg. via Find & Replave).

What i need to do is for e.g. i want to change:

A B C
1 =C3+C5 =C2+C9 =C10+C12
2 =D3+D5 =D2+D9 =D10+D12

TO:

A B C
1 =$C$3+$C$5 =$C$2+$C$9 =$C$10+$C$12
2 =$D$3+$D$5 =$D$2+$D$9 =$D$10+$D$12

Any ideas?

regards,

Copying Formulas From A Range Of Cells Without Changing The Absolute Or Relative References In Excel 2007 - Excel

View Content

1. Select the range of cells containing the formulas and press Ctrl+H or
select Home -> Find & Select (in Editing Group) -> Replace to open Find & Replace dialog box.
2. In the Find what box, type the equal (=) symbol.
3. In the Replace with box, type the # symbol (to change the formulas to text).
4. Click Replace All, and then click Close.
5. Copy and paste the cells to a new location.
6. Repeat steps 1 through 3 while reversing the # and = symbols (to change the text to formulas).

Change Relative To Absolute - Large Formulas - Excel

View Content

I have a considerable number of cells containing formulas exceeding 255 characters. I need to change all cell references in these formulas to absolute. Apart from manually changing each using F4 is there a way to convert all at once?

Thanks in advance

Batch Changing Relative References To Absolute References - Excel

View Content

I've got 4 columns of data in one spreadsheet that are linked to a source spreadsheet. It turns out that the data that I actually need to link to is 13 columns to the right of what I thought it should have been in the source spreadsheet. I can copy and paste the cells in the main spreadsheet 13 columns to the right, and they will be linking to the correct values in the source spreadsheet, but I can't seem to figure out a way to move those formulas back 13 columns to the left without the formulas changing. Is there a way that I can make them all absolute references to the source sheet or is there another easier way to get the correct links working?

Thank you.

How Do I Mix Relative & Absolute References? - Excel

View Content

Hi,

I want to insert this formula into my spreadsheet, but using VBA:

K2: =J2/$N$2

Then copy down - I have the copying part figured out, but unclear how to put this formula into VBA?

K100 will = J100/$N$2, etc.

Thanks

Random Tutorials

Make a Thermometer Style Chart in Excel
(Intermediate)

Net Present Value / Discounted Cash Flow Calculations
(Intermediate)

Extract Text from Cells - Intermediate Example
(Intermediate)

Lookups With MATCH() and INDEX() Functions
(Intermediate)

AND(), OR(), and IF() Statements/Formulas
(Intermediate)

Link Cells Between Worksheets
(Easy)

Submit Inquiry Here

Prices From $10