Change formulas from relative addressing to absolute?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Portwood - 30 May 2008 00:26 GMT

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

Reply to this Message

Gord Dibben - 30 May 2008 00:40 GMT

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
[quoted text clipped - 19 lines]
>Thanks in advance,
>David Portwood

Reply to this Message

David Portwood - 31 May 2008 15:55 GMT

Your code worked very well. Thank you, Gord.

> Here is a set of macros for changing cell references.
>
[quoted text clipped - 63 lines]
>>Thanks in advance,
>>David Portwood

Reply to this Message

Gary Keramidas - 30 May 2008 01:08 GMT

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

Signature

Gary

Reply to this Message