Hi I have numerous formulas with relative cells in them eg. a5, c6,
etc
I would like to change then to absolute values ($a$5, $c$6, etc)with
the edit, replace .....

can anyone help

thanks


andrew


--
andrewm
------------------------------------------------------------------------
andrewm's Profile: http://www.excelforum.com/member.php...o&userid=23130
View this thread: http://www.excelforum.com/showthread...hreadid=388266

"andrewm" wrote in
message ...

Hi I have numerous formulas with relative cells in them eg. a5, c6,
etc
I would like to change then to absolute values ($a$5, $c$6, etc)with
the edit, replace .....


Find - a - Replace with - $a$ - replace all.

Now that's not very hard, is it?

Be aware that Excel will change ALL a's into $a$, including those in text
and in addresses like AA12

Here is what Gord Dibben answered some time ago

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
================================================== ==========


Sharon


You would need VBA to make global changes to cell references.


Here are four........


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


Sub AbsoluteRow()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlAbsRowRelColumn)
End If
Next
End Sub


Sub AbsoluteCol()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelRowAbsColumn)
End If
Next
End Sub


Sub Relative()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelative)
End If
Next
End Sub


If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".


http://www.mvps.org/dmcritchie /excel/getstarted.htm


In the meantime..........


To create a General Module, hit ALT + F11 to open the Visual Basic Editor.


Hit CRTL + R to open Project Explorer.


Find your workbook/project and select it.


Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Run the macro by going to ToolMacroMacros.


Gord Dibben Excel MVP


On Mon, 18 Apr 2005 15:36:09 -0700,

"Gordon" wrote in message
...
"andrewm" wrote in
message ...

Hi I have numerous formulas with relative cells in them eg. a5, c6,
etc
I would like to change then to absolute values ($a$5, $c$6, etc)with
the edit, replace .....


Find - a - Replace with - $a$ - replace all.

Now that's not very hard, is it?

"Niek Otten" wrote in message
...
Be aware that Excel will change ALL a's into $a$, including those in text
and in addresses like AA12


Depends - if all the references are in one column, just select the column.