changing relative to absolute

P

Paul

Hello

I have a lot of functions in which there are relatative reference ranges, is
there a n easier way change them to absolute other than selecting each cell
individually and hitting F4?. Like can I select a range of cells and press a
button and those cells change to absolute
 
I

ivan.raiminius

Hi Paul,

you can do this programmaticaly. Search help for convertformula method.

Regards,

Ivan
 
G

Gord Dibben

Paul

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


Hello

I have a lot of functions in which there are relatative reference ranges, is
there a n easier way change them to absolute other than selecting each cell
individually and hitting F4?. Like can I select a range of cells and press a
button and those cells change to absolute
Click to expand...

Gord Dibben MS Excel MVP
 
Top