Adding round function to existing formula (Excel 97 SR1)

Topic

New Reply

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.

Reply | Quote

Replies

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:

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

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.

Reply | Quote

Edited by gwhitfield on 06-Nov-01 06:42.

Hyperlinks added

Check out Pearson Consulting article on the drawbacks of declaring the Object data type:

http://www.cpearson.com/excel/variables.htm

Reply | Quote

Is it something like this that you want?

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

Reply | Quote

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.

Reply | Quote

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 dollar-off 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.

Reply | Quote

T,

Can I recommend John Walkenbach’s FREE add-in called CellMath. It lets you handle rounding of cells as well as many other useful functions.

http://j-walk.com/ss/excel/files/general.htm

Ken

Reply | Quote

Thanks. That is a great page (J-walk) that I’ve been to many times. Just shows you you gotta be checking everything all the time! Hadn’t noticed that add-in.

Reply | Quote