How to chnge 35 relative cells to 35 absolute cells at one time.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can you change a massive amount of relative cells on one spread work sheet to
absolute without going into each cell and hitting F4?
 
Susan

By VBA only.


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

Can you change a massive amount of relative cells on one spread work sheet to
absolute without going into each cell and hitting F4?
Click to expand...

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

how to apply absolute cell referencing to multiple cells at once? 2
changing from relative to absolute cell reference in multiple form 1
Help with converting a block of cells with Absolute and mixed references to relative references 3
change relative to absolute references 2
Relative, absolute & mixed references in many cells 11
absolute refernces - range of cells 2
Changing Multiple Cells from Relative to Absolute Functions 2
The absolute referenced cell does not move when source cell moves 2

Back
Top