S
ScottS
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.
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.