Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    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.

  2. #2
    3 Star Lounger
    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.

  3. #3
    4 Star Lounger
    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>


  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 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

  5. #5
    5 Star Lounger
    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 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.

  6. #6
    2 Star Lounger
    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 add-in called CellMath. It lets you handle rounding of cells as well as many other useful functions.

    <A target="_blank" HREF=http://j-walk.com/ss/excel/files/general.htm>http://j-walk.com/ss/excel/files/general.htm</A>

    Ken

  7. #7
    5 Star Lounger
    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 (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.

  8. #8
    3 Star Lounger
    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 06-Nov-01 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>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •