Change formulas from relative addressing to absolute?

[David Portwood]

I have a large range which contains formulas that use absolute addressing as
opposed to relative. I would like to change these formulas to relative
addressing by adding $ before the row/column indicators. For example, I
would like to change formulas like:

=[C:\MyDirectory\MyWorkbook.xls]'Survey Count'!A4

to

=[C:\MyDirectory\MyWorkbook.xls]'Survey Count'!$A$4

This would apply to all cells in the range.

The above examples are supposed to be recognizable as external links. My
syntax may be off, since I am no guru and I am typing this at home from
memory. The important thing is that the formulas refer to individual cells,
not ranges of cells, which I hope would make the job easier, if it is
possible at all.

Is there a way to do this? Do I need a macro? Any help would be appreciated.

Thanks in advance,
David Portwood

 

[Gord Dibben]

Here is a set of macros for changing cell references.

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

 

[Gary Keramidas]

weird, but somebody else posted a similar question about 5 hours before.

 

[David Portwood]

Your code worked very well. Thank you, Gord.

Here is a set of macros for changing cell references.

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

I have a large range which contains formulas that use absolute addressing
as
opposed to relative. I would like to change these formulas to relative
addressing by adding $ before the row/column indicators. For example, I
would like to change formulas like:

=[C:\MyDirectory\MyWorkbook.xls]'Survey Count'!A4

to

=[C:\MyDirectory\MyWorkbook.xls]'Survey Count'!$A$4

This would apply to all cells in the range.

The above examples are supposed to be recognizable as external links. My
syntax may be off, since I am no guru and I am typing this at home from
memory. The important thing is that the formulas refer to individual
cells,
not ranges of cells, which I hope would make the job easier, if it is
possible at all.

Is there a way to do this? Do I need a macro? Any help would be
appreciated.

Thanks in advance,
David Portwood