Convert selected formula references from relative to absolute


M

M

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
 
Ad

Advertisements

G

Gord Dibben

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
 
W

Warren Easton

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?
 
G

Gord Dibben

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
 
W

Warren Easton

Thanks for that.
--
Regards
Warren


Gord Dibben said:
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
 
M

M

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
 
Ad

Advertisements

J

J

Hi, I have a reasonable, but limited knowledge of the advanced areas of
Excel. I want to do exactly as you have explained in these comments, but I
don't understand what the response from Gord means, or what I do with it. I
have multiple spreadsheets that I have linked and need to make a lot of
relevant cells turn into absolute cells. Can anyway explain this to me in
layman's terms?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top