changing from relative to absolute cell reference in multiple form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JAaron - 22 May 2006 07:18 GMT

Is there a way to change relative cell refernces in multiple formulas from
relative to absolute all at the same time, rather than highlighting refernce
and hitting F4 and changing them one at a time. I have to change several
hundred formula references from relative to absolute.

Reply to this Message

Ardus Petus - 22 May 2006 08:06 GMT

Sub AbsFormula()
Dim rcell As Range
For Each rcell In ActiveSheet.UsedRange.SpecialCells(xlFormulas)
rcell.Formula = Application.ConvertFormula( _
rcell.Formula, xlA1, xlA1, xlAbsolute)
Next rcell
End Sub

HTH
--
AP

> Is there a way to change relative cell refernces in multiple formulas from
> relative to absolute all at the same time, rather than highlighting
> refernce
> and hitting F4 and changing them one at a time. I have to change several
> hundred formula references from relative to absolute.

Reply to this Message