How do I change an Excel range of cells from relative to absolute.

[Guest]

I want to change, in a range of cells from relative cell adressing into
absolute cell adressing, not by changing each cel but change them all by one
command.

 

[wmjenner]

You can use Edit/Replace. If the range is fixed it's even better
because there's less risk of hosing something unintentionally. For
range A110

Under Edit/Replace:

Find What = A:
Replace With = $A:

Find What =
Replace With = :$D

Or, if the range is fixed for all, then

Find What = A110
Replace with = $A$1:$D$10

 

[Frank Kabel]

Hi
a repost from Gord Dibben:

----------------------------

Only through VBA macro.

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 Insert>Module. Paste the above code in there. Save
the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

Gord Dibben Excel MVP