Changing range of cells to absolute references?

[DCGringo]

I am trying to figure out a way to change a table of formulas wit
multiple cell references to absolute cell references. Does anyone kno
how to do this without going into each cell and editing each on
manually? I have used find/replace on other occations with grea
success, but this table has references that do not follow a commo
thread

 

[Bill Kuunders]

Go to http://www.asap-utilities.com/
and download the free add-in.
It has the option under formula's section, and much more.

Regards
Bill K

 

[Gord Dibben]

DC

VBA macro will do this. 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)
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)
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)
Next
End Sub

For info on getting started with Macros, visit David McRitchie's site.

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

Gord Dibben Excel MVP