Module to make all absolute references relative

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
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)
 
Thanks, that's great.

Bob Phillips said:
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)
Click to expand...
 
Back
Top