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,

Dana DeLouis just posted this in .misc:

From - Wed Aug 16 07:40:57 2006
From: "Dana DeLouis"
Subject: How do I replace forumulas with the round function in one go
Date: Wed, 16 Aug 2006 08:37:34 -0400
Newsgroups: microsoft.public.excel.misc

... then realise I wish I had entered the "round" function
Also considered a macro ...but don't know how to get this
to work over a large range of formulas.

I have a toolbar button that runs the following code.
Adjust if you always want it to round to 0.

Sub Round_Add()
'// = = = = = = = = = = = = = = = = = = = = = = = = = = =
'// Adds =ROUND( ) to Formulas in current Selection
'// May have more than one area selected
'// Avoids adding Round to beginning if already used
'// = = = = = = = = = = = = = = = = = = = = = = = = = = =

Dim BigRng As Range
Dim Rng As Range
Dim Cell As Range
Dim Equ As String
Dim iRound As Integer

On Error Resume Next
If Not (TypeOf Selection Is Range) Then Exit Sub
Set BigRng = Selection.SpecialCells(xlFormulas)
If BigRng Is Nothing Then Exit Sub

iRound = InputBox("Round to how many digits?", , 2)

Equ = "=Round(#,n_)"
Equ = Replace(Equ, "n_", iRound)

For Each Rng In BigRng.Areas
For Each Cell In Rng.Cells
If Not Cell.Formula Like "=ROUND(*" Then
Cell.Formula = Replace(Equ, "#", Mid$(Cell.Formula, 2))
End If
Next Cell
Next Rng
End Sub

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003

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,

--

Dave Peterson

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

Hi Jim,

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.

Check this page:

http://www.jkp-ads.com/Articles/FormulaWrapper00.htm

Use the code showed in there, but change this line of code:

sFormulaTemplate = "=IF(ISERROR(_form_),"""",_form_)"

to:

sFormulaTemplate = "=SUM(_form_)"

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

WOWWWWWW - to all three of you guys !!
Thanks,

Jim

"Jan Karel Pieterse" wrote:

Hi Jim,

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.

Check this page:

http://www.jkp-ads.com/Articles/FormulaWrapper00.htm

Use the code showed in there, but change this line of code:

sFormulaTemplate = "=IF(ISERROR(_form_),"""",_form_)"

to:

sFormulaTemplate = "=SUM(_form_)"

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com