Absolutes in Formulas

S

steph44haf

Is there a faster way to change a bunch of formulas to have absolute values,
instead of going into each one and doing the F4?

~Steph
 
B

Bernie Deitrick

Steph,

Select the cells with formulas, and run the macro below.

HTH,
Bernie
MS Excel MVP


Sub ConvertToAbsoluteReferences()
Dim myCell As Range
Dim storedCalc As Variant
With Application
storedCalc = .Calculation
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
For Each myCell In Intersect(Selection, Selection.SpecialCells(xlCellTypeFormulas))
myCell.Formula = Application.ConvertFormula( _
myCell.Formula, xlA1, xlA1, xlAbsolute)
Next myCell
.ScreenUpdating = True
.EnableEvents = True
.Calculation = storedCalc
End With
End Sub
 
Top