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

  • Thread starter Susan A at Millennium Medical
  • Start date
S

Susan A at Millennium Medical

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

Gord Dibben

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
Help with converting a block of cells with Absolute and mixed references to relative references 3
changing from relative to absolute cell reference in multiple form 1
External cell reference between two excel worksheets both in One Drive 0
Multiple AND OR functions 5
Relative, absolute & mixed references in many cells 11
Absolute and Relative Cell References 1
Formula autofill to cells below – formula arguments are cell areas in different columns 1

Top