Round(cell,0) after the Fact
Jim May wrote:
Can this be done?
In my sheet All formulas are strictly =Sum()
Is there a MACRO (VBA) that can CHANGE All = Sum()'s formulas
to =Round(Sum(),0) at once ?
If so, could someone share code.
I've been trying now for an hour, but without success
including a google search.
TIA,
Hi Jim,
Try this out on a backup copy of your sheet. Select the range of cell
first then run the code...
Public Sub ChangeToRoundSum()
Dim rngCell As Range
Dim strFormula As String
For Each rngCell In Application.Selection
If Left(rngCell.Formula, 4) = "=SUM" _
And Left(rngCell.Formula, 10) < "=ROUND(SUM" Then
strFormula = Right(rngCell.Formula, Len(rngCell.Formula) - 1)
rngCell.Formula = "=ROUND(" & strFormula & ",0)"
End If
Next
End Sub
Ken Johnson
|