How do I anchor hundreds of cells easily

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 <[email protected]> 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 <[email protected]> wrote:

>Hi! Gord,
>
>Thank you so much! The macro works very well!
>
>Ken

Gord Dibben MS Excel MVP