Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Inserting Certain Operations (2000)

    I have a very large worksheet that contains many (>250) formulas that contain both hard data (such as 125*D3), as well as links to other worksheets/files. What I would like to do is "apply", say, a rounding convention to all of the cells with formulas, such that each formula is rounded to the nearest whole number. How can I do this without going into and editing each cell?
    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting Certain Operations (2000)

    You can create a macro to handle this. The macro doesn't have to be stored in the workbook itself, it can be in any workbook. Select a range containing the cells with formulas before running the macro (it may also contain cells with constant values and empty cells).

    Sub RoundFormulas
    Dim oCell As Range
    For Each oCell In Selection.Cells
    If oCell.HasFormula Then
    oCell.Formula = "=ROUND(" & Mid(oCell.Formula, 2) & ",0)"
    End If
    Next oCell
    Set oCell = Nothing
    End Sub

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inserting Certain Operations (2000)

    I would recommend:

    <pre>If oCell.HasFormula _
    <font color=red>and left(ocell.formula, 7) <> "=ROUND(" </font color=red> Then
    oCell.Formula = "=ROUND(" & Mid(oCell.Formula, 2) & ",0)"</pre>


    so if it ever needs to be rerun, you won't add it to one that has already been modified or ones that have different roundings.

    Steve

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Inserting Certain Operations (2000)

    Thanks, Hans. One last Q: what if I also have just plain data (numbers, not formulas)?

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Certain Operations (2000)

    The line:

    <pre> If oCell.HasFormula Then
    </pre>


    prevents the code from applying the rounding to any cell that does not contain a formula. What do you want it to do?
    Legare Coleman

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Inserting Certain Operations (2000)

    Thanks, Steve.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Inserting Certain Operations (2000)

    Thanks, Legare. It was just an afterthought that I would like the solution to apply to any cell, whether it contains a formula or hard data.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Inserting Certain Operations (2000)

    Thanks, Hans.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inserting Certain Operations (2000)

    You could do something like this. It converts all number constants to a formula that rounds it (you could round the number and place the rounded value in the cell if desired) and then converts all number formulas to be the round function.

    <pre>Option Explicit
    Sub RoundAllNumbers()
    Dim rSelect As Range
    Dim rng As Range
    Dim rCell As Range
    Set rSelect = Selection
    Set rng = Nothing
    On Error Resume Next
    Set rng = rSelect.SpecialCells(xlCellTypeConstants, xlNumbers)
    On Error GoTo 0
    If Not rng Is Nothing Then
    For Each rCell In rng
    rCell.Formula = "=ROUND(" & _
    rCell.Value & ",0)"
    Next
    End If
    Set rng = Nothing
    On Error Resume Next
    Set rng = rSelect.SpecialCells(xlCellTypeFormulas, xlNumbers)
    On Error GoTo 0
    If Not rng Is Nothing Then
    For Each rCell In rng
    If Left(rCell.Formula, 7) <> "=ROUND(" Then
    rCell.Formula = "=ROUND(" & _
    Mid(rCell.Formula, 2) & ",0)"
    End If
    Next
    End If

    Set rCell = Nothing
    Set rng = Nothing
    Set rSelect = Nothing
    End Sub</pre>


    Another option is to format the entire worksheet to the desired format (1, 2,3 cells or whatever) then go to tools - options - calculation (tab) and check "precision as displayed. This will (permanently) truncate the precision to the number of places each is set for.

    Steve

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Inserting Certain Operations (2000)

    Thanks, Steve, your code works like a charm.

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Inserting Certain Operations (2000)

    Steve,
    You very aptly helped me out a couple of years ago re this. I have since moved, and forgotten how to "run" this routine. I have inserted it into a module, but don't know how to run it.
    HELP!!!

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting Certain Operations (2000)

    Option 1:
    - Select the cells you want to process.
    - Press Alt+F8 or select Tools | Macro | Macros...
    - Select RoundAllNumbers in the list of macros.
    - Click Run.

    Option 2:
    - Select the cells you want to process.
    - Press Alt+F11 to switch to the Visual Basic Editor.
    - Click anywhere in the RoundAllNumbers macro code.
    - Press F5 to run the macro.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting Certain Operations (2000)

    What is the name of the module in which you stored the macro?

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Inserting Certain Operations (2000)

    Thanks Hans. While option 2 worked alright, no macro named "RoundAllNumbers" showed up in the macro window. Any ideas?

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Inserting Certain Operations (2000)

    Module 3. Here is a sample.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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