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!

How do I anchor hundreds of cells easily
 

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!

How do I anchor hundreds of cells easily
 

Hi! Gord,

Thank you so much! The macro works very well!

Ken

How do I anchor hundreds of cells easily
 

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