Change Mass Relative To Absolute

[Bear]

Hi all

I want to change in a huge spreadsheet, all formulas to absolute referencing. All my formula is at single column, but the problem is, part of my formula has $, part is none and I wanna turn all formula to absolute

Original formula
SUMIF($E$9:$E$47,"A*",W$9:W$47)
SUMIF($E$9:$E$47,"b*",X$9:X$47)
SUMIF($E$9:$E$47,"c*",Y$9:Y$47)

Want the result show
SUMIF($E$9:$E$47,"A*",$W$9:$W$47)
SUMIF($E$9:$E$47,"b*",$X$9:$X$47)
SUMIF($E$9:$E$47,"c*",$Y$9:$Y$47)

I don't feel OK at changing one at a time. Is there any other way to do it ???

Thanks for your time.

 

[Mike H..]

Hi all

I want to change in a huge spreadsheet, all formulas to absolute referencing. All my formula is at single column, but the problem is, part of my formula has $, part is none and I wanna turn all formula to absolute

Original formula
SUMIF($E$9:$E$47,"A*",W$9:W$47)
SUMIF($E$9:$E$47,"b*",X$9:X$47)
SUMIF($E$9:$E$47,"c*",Y$9:Y$47)

Want the result show
SUMIF($E$9:$E$47,"A*",$W$9:$W$47)
SUMIF($E$9:$E$47,"b*",$X$9:$X$47)
SUMIF($E$9:$E$47,"c*",$Y$9:$Y$47)

I don't feel OK at changing one at a time. Is there any other way to do it ???

Thanks for your time.

Hi,

ALT+F11 to open vb editor, right click 'Thisworkbook' and insert module and paste the code below in on the right. Select all the formula cells and run the code.

Sub versive()
Dim MyRange As Range, c As Range
Set MyRange = Selection
For Each c In MyRange
If c.HasFormula Then
c.Formula = Application.ConvertFormula(c.Formula, xlA1, xlA1, xlAbsolute)
End If
Next
End Sub