Module to make all absolute references relative

[Guest]

I'm using absolute references to allow a set of formulas to be copied across
and down a spreadsheet with only certain values changing relatively. After
the formulas are in place, I need all absolute references to be returned to
relative references so the sheet will still function when it is reformatted,
parts added/removed etc...

Couldn't a simple module be written to remove all "$" characters from cells
in the sheet?

 

[Peter T]

Probably just as quick to do a manual search & replace-all (ctrl-h) in Excel
as would be to find and run a macro. Or record a macro while you do that.

Regards,
Peter T

 

[Bob Phillips]

Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, xlA1, ,
xlRelative)
End If
Next cell


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks, that's great.

Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, xlA1, ,
xlRelative)
End If
Next cell


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)