How to remove the cell reference in a sheet at once
You can use the editreplace as suggested.
Or use a macro to make changes in selected cells.
Sub Absolute()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlAbsolute)
End If
Next
End Sub
Sub AbsoluteRow()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlAbsRowRelColumn)
End If
Next
End Sub
Sub AbsoluteCol()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
End If
Next
End Sub
Sub Relative()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlRelative)
End If
Next
End Sub
Gord Dibben MS Excel MVP
On Mon, 24 May 2010 22:12:03 -0700 (PDT), The Greek
wrote:
Hi,
I have a sheet where there are many cells are fix (reference cells)
using the F4 key. Is there any way i can remove the effect of the F4
without going to each formula and them keep pressing F4 to remove the
reference???
|