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

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