Changing cell references from Relative to Absolute
Posted by Chris L on March 20, 2001 11:21 PM
Is it possible to change formulas in a large range of cells from RELATIVE to ABSOLUTE simply?
Posted by Celia on March 21, 2001 12:57 AM
Chris
Try this macro :-
Sub Relative_to_Absolute()
Dim cell As Range, fmlaCells As Range
On Error GoTo e
Set fmlaCells = Cells.SpecialCells(xlFormulas)
On Error GoTo 0
For Each cell In fmlaCells
cell.Formula = Application.ConvertFormula( _
cell.Formula, xlA1, xlA1, xlAbsolute)
Next cell
e:
End Sub
Thr above should convert all formula cell refs on the active worksheet to absolute refs.
If you want to convert only selected cells, then change the line that reads :
Set fmlaCells = Cells.SpecialCells(xlFormulas)
to:
Set fmlaCells = Selection.SpecialCells(xlFormulas)
Celia
Posted by Dave Hawley on March 21, 2001 3:57 AM
Hi Chris
As I hate Loops (there are painfully slow), I have modified Celias code (Which does work!) to exclude it.
Sub Relative_to_Absolute()
fmlaCells As Range
On Error Resume Next
Set fmlaCells = Cells.SpecialCells(xlFormulas)
fmlaCells.Formula = Application.ConvertFormula _
(fmlaCells.Formula, xlA1, xlA1, xlAbsolute)
End Sub
OzGrid Business Applications
Posted by David Hawley on March 21, 2001 4:00 AM
Oops Typo! Took too much out :o)
As I hate Loops (there are painfully slow), I have modified Celias code (Which does work!) to exclude it.
Sub Relative_to_Absolute()
Dim fmlaCells As Range
On Error Resume Next
Set fmlaCells = Cells.SpecialCells(xlFormulas)
fmlaCells.Formula = Application.ConvertFormula _
(fmlaCells.Formula, xlA1, xlA1, xlAbsolute)
End Sub
OzGrid Business Applications
Posted by Celia on March 21, 2001 4:32 AM
So what is your revised macro?
As I hate Loops (there are painfully slow), I have modified Celias code (Which does work!) to exclude it. Sub Relative_to_Absolute()
Posted by Celia on March 21, 2001 4:45 AM
OK, I see - missed the "Dim"
Posted by Celia on March 21, 2001 5:02 AM
Processing time for loops
Dave's advice to avoid loops where possible is very sound. The macros posted are good examples.
In a worksheet containing 31,650 formula cells, the macro with the loop took 2 minutes and 42 seconds to run. The macro without the loop took only 2 seconds.
Celia
Posted by Celia on March 21, 2001 5:21 AM
Seems to be a problem with this code
As I hate Loops (there are painfully slow), I have modified Celias code (Which does work!) to exclude it. Sub Relative_to_Absolute()
Dave
Your macro does not seem to work if the formula cells are not contiguous.
Celia
Posted by Dave Hawley on March 21, 2001 5:55 AM
Not so!
It works on my PC. I just tried again to make sure!
OzGrid Business Applications
Posted by Celia on March 21, 2001 5:58 AM
Strange!
I've also tried it several times and it doesn't work. I'll send you a workbook.
Posted by Chris L on March 21, 2001 1:32 PM
Re: Strange! - THANKS Celia & Dave
Celia's macro worked on my workbook, but not Dave's. This has probably saved me from going prematurely bald (pulling out my hair)! Thanks
Posted by Dave Hawley on March 21, 2001 5:37 PM
Re: Strange! - THANKS Celia & Dave
Hi Chris
This is a strange one. On some layouts my macro works in a jiff on non-contigous ranges, yet on other layouts it falls over..
Oh well you have it fixed that's all that matters.
OzGrid Business Applications
Posted by Celia on March 23, 2001 3:17 AM
Further info
Chris
The Microsoft Knowledge Base provides a macro example for converting formulas that may of interest to you :-
http://support.microsoft.com/support/kb/articles/Q116/0/28.asp?LN=EN-US&SD=gn&FR=0&qry=convertformula&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=XLW
Their macro example also loops through each cell.
Celia
