On Jul 23, 6:02 pm, Tom Reetz
wrote:
Thanks a lot for your prompt and accurate reponse! I suspected that a macro
would be required, but had hoped that a Function would do the trick. But it
is good to know there is a way. Thanks again.
--
Tom



"Gord Dibben" wrote:
Here are 4 macros.

The fourth one will do what you want.

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

Gord Dibben MS Excel MVP

On Mon, 23 Jul 2007 15:10:07 -0700, Tom Reetz
wrote:

When I am building a worksheet, I use $ a lot to permit copying. After I am
satisfied with the result, I want to convert all the formulas from absolue to
relative. But the only way I know to do it is one cell at a time. Isn't
there an easier way?- Hide quoted text -

- Show quoted text -

From the Edit menu /Find / Replace and put $ in the find window and
leave the Replace widow blank, and hit Replace All.

ed (who hates macros)