Results 1 to 8 of 8

20011025, 14:19 #1
 Join Date
 May 2001
 Location
 Philadelphia, Pennsylvania
 Posts
 676
 Thanks
 0
 Thanked 0 Times in 0 Posts
Adding round function to existing formula (Excel 97 SR1)
I often inherit worksheets which ought to get the round function added to alot of formulas. I've been trying to think up a Macro that would take the selected cell or cells and insert at the appropriate places the Round function arguments. Would I have to convert the existing formula(s) to something else to do that? Or say something like cell.formula= "=Round("+[formula variable]=",2)" ? Is that it? Can I select a large range to cover all the stuff at once? I will work on this, but some confirmation would help alot. I think I know how to connect it to a toolbar button control. I can do that later.
I have been reading some excel programming books, and, as an accountant, am looking not so much to develop full applications as to simplify and automate such tasks as above. Thanks.

20011025, 15:46 #2
 Join Date
 Dec 2000
 Location
 Vancouver, Br. Columbia, Canada
 Posts
 268
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Adding round function to existing formula (Excel 97 SR1)
I think your approach of appending (okay "prepending and appending," for the pedants here!) is the right way to go.
The following code will do the trick:
<pre>Sub MakeRound()
Dim CellText As String
Dim Cll As Object
For Each Cll In Selection
CellText = Cll.Formula
If Left(CellText, 1) = "=" Then CellText = Right(CellText, Len(CellText)  1)
CellText = "=round(" & CellText & ",2)"
Cll.Formula = CellText
Next Cll
End Sub
</pre>
This will round functions like sum or NPV calculations as well, and can be applied to a whole range. The routine will not fail if applied to a blank cell, although the resulting formula of "=round(,2)" will return a zero value  that is an aesthetic defect if you have zero values set to visible. Applying the routine to a label results in a name error, since excel interpretes the existing text as a name that should return a numeric value.
Both of these defects can be avoided by being more careful in applying the routine. As you say, it is easy to keep it in personal.xls and attach it to a tool button if you need it often.

20011025, 15:53 #3
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Adding round function to existing formula (Excel 97 SR1)
Is it something like this that you want?
<pre>Sub ROUNDTODEC()
Dim cell As Range
Dim StrFormula As String
Dim digits As String
digits = "1" 'rounding to digits number of decimals
For Each cell In ActiveSheet.UsedRange
If cell.HasFormula And Left$(cell.Formula, 6) <> "=ROUND" Then
StrFormula = "=ROUND(" & Right$(cell.Formula, Len(cell.Formula)  1) & _
"," & digits & ")"
cell.Clear
cell.Formula = StrFormula
End If
Next
End Sub
</pre>

20011025, 16:47 #4
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Adding round function to existing formula (Excel 97 SR1)
Thank you, Hans. It's certainly something I want and never got around to, except I made two tweaks:
digits = InputBox("How many digits? ")
For Each cell In Selection
but as I read the original request, I think Torquemada wants to be able to edit a part of a function within the formula, such as editing
=SUM(A1:A10)*D4
to
=SUM(A1:A10)*ROUND(D4,2)
I have need of this capability myself, but don't think it can be done.John ... I float in liquid gardens
UTC 7ąDS

20011025, 17:38 #5
 Join Date
 May 2001
 Location
 Philadelphia, Pennsylvania
 Posts
 676
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Adding round function to existing formula (Excel 97 SR1)
Thanks to everyone. I am slowly getting the drift of how to think in the terms of "objects" and so on. The whole purpose of this is to try and eliminate the dreaded dollaroff effects seen in financial schedules when formatting vs rounding effects accumulate. I don't know if this will solve the whole issue, but being able to go back, after a large schedule is setup, and insert the universal round function is an approach I want to try. These examples will be great to use.

20011103, 18:39 #6
 Join Date
 Dec 2000
 Location
 Sault Ste. Marie, Michigan, USA
 Posts
 102
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Adding round function to existing formula (Excel 97 SR1)
T,
Can I recommend John Walkenbach's FREE addin called CellMath. It lets you handle rounding of cells as well as many other useful functions.
<A target="_blank" HREF=http://jwalk.com/ss/excel/files/general.htm>http://jwalk.com/ss/excel/files/general.htm</A>
Ken

20011105, 13:15 #7
 Join Date
 May 2001
 Location
 Philadelphia, Pennsylvania
 Posts
 676
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Adding round function to existing formula (Excel 97 SR1)
Thanks. That is a great page (Jwalk) that I've been to many times. Just shows you you gotta be checking everything all the time! Hadn't noticed that addin.

20011105, 18:42 #8
 Join Date
 Mar 2001
 Location
 Silicon Valley, California, USA
 Posts
 273
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Adding round function to existing formula (Excel 97 SR1)
<P ID="edit" class=small>Edited by gwhitfield on 06Nov01 06:42.</P>Hyperlinks added
Check out Pearson Consulting article on the drawbacks of declaring the Object data type:
<A target="_blank" HREF=http://www.cpearson.com/excel/variables.htm>http://www.cpearson.com/excel/variables.htm</A>