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

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.

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 A1:D10

Under Edit/Replace:

Find What = A:
Replace With = $A:

Find What = :D
Replace With = :$D

Or, if the range is fixed for all, then

Find What = A1:D10
Replace with = $A$1:$D$10


--
wmjenner


------------------------------------------------------------------------
wmjenner's Profile: http://www.excelforum.com/member.php...fo&userid=5282
View this thread: http://www.excelforum.com/showthread...hreadid=278751

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 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

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


Jrhenk wrote:
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.