How do I convert a selected range of cell references within formulas from
relative to absolute?
For example cells within a column of cells are set up as:
='sheet name'!B9
='sheet name'!B10 etc.
How do I convert or change all the references within the selected range of
cells to:
='sheet name'!$B$9
='sheet name'!$B$10 etc.

Thank you,
M

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


Gord Dibben MS Excel MVP

On Thu, 30 Oct 2008 08:59:02 -0700, M wrote:

How do I convert a selected range of cell references within formulas from
relative to absolute?
For example cells within a column of cells are set up as:
='sheet name'!B9
='sheet name'!B10 etc.
How do I convert or change all the references within the selected range of
cells to:
='sheet name'!$B$9
='sheet name'!$B$10 etc.

Thank you,
M

Hi Gord,

I'm new at this but would this also work in reverse from absolute to
relative if you change the text "absolute" to "relative" in the Macro below?
--
Regards
Warren


"Gord Dibben" wrote:

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


Gord Dibben MS Excel MVP

On Thu, 30 Oct 2008 08:59:02 -0700, M wrote:

How do I convert a selected range of cell references within formulas from
relative to absolute?
For example cells within a column of cells are set up as:
='sheet name'!B9
='sheet name'!B10 etc.
How do I convert or change all the references within the selected range of
cells to:
='sheet name'!$B$9
='sheet name'!$B$10 etc.

Thank you,
M

Yes.

Here is the full set........................

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

On Thu, 30 Oct 2008 09:30:15 -0700, Warren Easton
wrote:

Hi Gord,

I'm new at this but would this also work in reverse from absolute to
relative if you change the text "absolute" to "relative" in the Macro below?

Thanks for that.
--
Regards
Warren


"Gord Dibben" wrote:

Yes.

Here is the full set........................

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

On Thu, 30 Oct 2008 09:30:15 -0700, Warren Easton
wrote:

Hi Gord,

I'm new at this but would this also work in reverse from absolute to
relative if you change the text "absolute" to "relative" in the Macro below?

Gord/ Warren and group - THANKS!!!
I sometimes spend 'hours' trying to figure 'it' out and finally give it up
and ask -
-Minutes- and you folks have the answer!!
Thanks,
M