Hi!

I have hundreds of cells with formula that needs to be anchored for both
columns and rows. Is any easy way to do it?

Thank you very much!

Ken

Will a macro solution be OK with you?

Try these. Ignores cells without formulas.

Sub Absolute()
'$A$1
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()
'A$1
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()
'$A1
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()
'A1
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 Thu, 22 Jun 2006 11:19:01 -0700, Ken wrote:

Hi!

I have hundreds of cells with formula that needs to be anchored for both
columns and rows. Is any easy way to do it?

Thank you very much!

Hi! Gord,

Thank you so much! The macro works very well!

Ken

Appreciate the feedback.

Thanks Ken


Gord

On Fri, 23 Jun 2006 11:14:02 -0700, Ken wrote:

Hi! Gord,

Thank you so much! The macro works very well!

Ken

Gord Dibben MS Excel MVP