How do I anchor hundreds of cells easily

G

Guest

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!
 
G

Gord Dibben

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

add hyperlink using sheet names and cells(i,j) 3
Sorting complex worksheets 1
Anchor Doesn't work 3
How do I anchor a cell/create absolute ref. while clicking on it? 1
copy formla that refers to another worksht down 2
Conditional formatting changing relative when copied to other cell 1
Making a structured reference absolute? 5
How convert Numeric number in words 2

Top