change relative to absolute references

S

Steve Mackay

Is there a way to change relative references to absolute references in a
batch. I want to change a whole sheet of relative references to absolute
without selecting the cells one at a time and using F4. Or, even
better...is there a way to copy a group of cells together and paste the
exact formla somewhere else, even if they are relative references?

Thanks
 
R

RagDyer

You can "unformulate" a formula, or range of formulas, copy them to a new
location, and then "re-formulate" them.

Select all the formulas you wish to copy.
Then <Edit> <Replace>.

In the "Find What", enter the equal sign (=).
In the "Replace With", enter
<space> =

This makes them all text.

Now just "Copy" and "Paste" to the new locatiion, and do the reverse.

<Edit> <Replace>.

In the "Find What", enter
<space> =
In the "Replace With", enter
the equal sign (=).

And you should have all your formulas copied, with their original cell
references intact.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Is there a way to change relative references to absolute references in a
batch. I want to change a whole sheet of relative references to absolute
without selecting the cells one at a time and using F4. Or, even
better...is there a way to copy a group of cells together and paste the
exact formla somewhere else, even if they are relative references?

Thanks
 
P

Peo Sjoblom

Here are some code

Sub ReltoAbs()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsolute)
Next
End Sub

Sub AbstoRel()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlRelative)
Next
End Sub

Sub RelColAbsRows()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsRowRelColumn)
Next
End Sub

Sub RelRowsAbsCol()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlRelRowAbsColumn)
Next
End Sub



press Alt + F11, click insert>module and paste in the above, press Alt + Q
to close the VBE,
select the range and press Alt + F8, then double click ReltoAbs, to reverse
click AbstoRel



--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Top