Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update References To Absolute
I have a situation where users have long formulas with external references.
I need to convert them to absolute. I tried using this code but it resulted in VALUE# errors Sub Absolute() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula( _ cell.Formula, xlA1, xlA1, xlAbsolute) End If Next End Sub Here's an example of a formula I need it to work with. CHOOSE($C$1,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!F620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!G620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!H620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!I620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!J620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!K620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!L620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!M620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!N620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!O620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!P620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!Q620) Thanks in advance for your help. -- Scott S |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update References To Absolute
don't know about your formula because i don't have all of those references, but
this worked for me, with a formula in C1 range("D1")= application.ConvertFormula(range("C1").Formula,xlA 1,xla1,xlabsolute) =Sheet2!B1+A1 changes to =Sheet2!$B$1+$A$1 -- Gary "ScottS" wrote in message ... I have a situation where users have long formulas with external references. I need to convert them to absolute. I tried using this code but it resulted in VALUE# errors Sub Absolute() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula( _ cell.Formula, xlA1, xlA1, xlAbsolute) End If Next End Sub Here's an example of a formula I need it to work with. CHOOSE($C$1,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!F620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!G620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!H620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!I620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!J620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!K620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!L620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!M620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!N620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!O620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!P620,'O:\EVERYONE\User\Reporting\[Data File.xls]Actual'!Q620) Thanks in advance for your help. -- Scott S |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert relative cell references to absolute cell references via amacro? | Excel Discussion (Misc queries) | |||
Help with converting a block of cells with Absolute and mixed references to relative references | Excel Worksheet Functions | |||
Absolute References | Excel Worksheet Functions | |||
absolute references etc in VBA | Excel Programming | |||
Absolute references | Excel Programming |