change relative to absolute references

  • Thread starter Thread starter Steve Mackay
  • Start date Start date
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
 
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
 
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)
 
Back
Top