 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
| |
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.comDim RdoRange As RangeDim i As IntegerDim 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 = NothingEnd SubSub 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 = NothingEnd SubExcel 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. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader PackageTechnical 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