Locking cell references

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

Guest

Is there a quick way I can lock a cell reference to a group of existing formulas without going through each one? I have thousands in a pre-exising file and I can't copy because the references have almost no pattern
 
Shin

Only through VBA macro.

Sub Absolute()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula _
(cell.Formula, xlA1, xlA1, xlAbsolute)

''line above can be altered for varying references as below
''(cell.Formula, xlA1, xlA1, xlAbsRowRelColumn)
''(cell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
''(cell.Formula, xlA1, xlA1, xlRelative)

End If
Next
End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord Dibben Excel MVP
 
Back
Top