changing multiple cells from relative to absolute reference
Is it possible to change a large range of very complex formulas from relative
reference to absolute reference. Something other than the replace command? thanks, Mike |
Hi Mike
one option that might work if the cell references you want to replace are used multiple times and that is to use range names, for example if you have the formulas =A1*A2 =A1*A5 =A1+A10 and you now want to make A1 absolute, select A1, click in the name box (box to left of formula bar above column A) and type a name for the range e.g. Rng_A1 and press ENTER (v. important) now choose insert / name / apply and click ok you will end up with =Rng_A1*A2 =Rng_A1*A5 =Rng_A1+A10 and range names are absolute references. Please test this on a copy of your workbook first to see if it achieves what you want. Cheers JulieD "Mike" wrote in message ... Is it possible to change a large range of very complex formulas from relative reference to absolute reference. Something other than the replace command? thanks, Mike |
You can do it with the following macro:
Sub ChangeFormulas() Dim Rng As Range For Each Rng In Range("A1:A10").SpecialCells(xlCellTypeFormulas) If Rng.HasArray Then Rng.FormulaArray = Application.ConvertFormula( _ fromreferencestyle:=xlA1, Formula:=Rng.Formula, toabsolute:=True) Else Rng.Formula = Application.ConvertFormula( _ fromreferencestyle:=xlA1, Formula:=Rng.Formula, toabsolute:=True) End If Next Rng End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mike" wrote in message ... Is it possible to change a large range of very complex formulas from relative reference to absolute reference. Something other than the replace command? thanks, Mike |
Chip, thanks for the macro, i'll give it a shot.
"Chip Pearson" wrote: You can do it with the following macro: Sub ChangeFormulas() Dim Rng As Range For Each Rng In Range("A1:A10").SpecialCells(xlCellTypeFormulas) If Rng.HasArray Then Rng.FormulaArray = Application.ConvertFormula( _ fromreferencestyle:=xlA1, Formula:=Rng.Formula, toabsolute:=True) Else Rng.Formula = Application.ConvertFormula( _ fromreferencestyle:=xlA1, Formula:=Rng.Formula, toabsolute:=True) End If Next Rng End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mike" wrote in message ... Is it possible to change a large range of very complex formulas from relative reference to absolute reference. Something other than the replace command? thanks, Mike |
JulieD, that is a very good idea. Unfortunately, there are to many different
cells involved. Mike "JulieD" wrote: Hi Mike one option that might work if the cell references you want to replace are used multiple times and that is to use range names, for example if you have the formulas =A1*A2 =A1*A5 =A1+A10 and you now want to make A1 absolute, select A1, click in the name box (box to left of formula bar above column A) and type a name for the range e.g. Rng_A1 and press ENTER (v. important) now choose insert / name / apply and click ok you will end up with =Rng_A1*A2 =Rng_A1*A5 =Rng_A1+A10 and range names are absolute references. Please test this on a copy of your workbook first to see if it achieves what you want. Cheers JulieD "Mike" wrote in message ... Is it possible to change a large range of very complex formulas from relative reference to absolute reference. Something other than the replace command? thanks, Mike |
All times are GMT +1. The time now is 03:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com