Rounding off

[shreeram.maroo]

Hi,

I have many tables in one word file and those tables also contain values.
Values are in two decimals. Is there any way through which I can round off the values in all tables to no decimals ?

Thanks,
Shreeram

[HansV]

See if this macro does what you want. Please test on a copy of your document.

Code: Select all

Sub RoundValues()
    Dim tbl As Table
    Dim cel As Cell
    Dim s As String
    Application.ScreenUpdating = False
    For Each tbl In ActiveDocument.Tables
        tbl.Select
        For Each cel In Selection.Cells
            s = cel.Range.Text
            s = Left(s, Len(s) - 2)
            If IsNumeric(s) Then
                cel.Range.Text = Round(s, 0)
            End If
        Next cel
    Next tbl
    Application.ScreenUpdating = True
End Sub

[shreeram.maroo]

Hi Hans,

That's working perfectly. Just a small issue is that it changes the number styles.
My number style includes comma separators and negative values are in bracket(). Like 29,000 or (29,000) - The macro changes it to 29000 or -29000

Thanks,
Shreeram

[Rudi]

This is a guess, but try changing this line:

Code: Select all

                cel.Range.Text = Round(s, 0)

to this:

Code: Select all

                cel.Range.Text = Format(Round(s, 0), "#,##0;(#,##0)")

[shreeram.maroo]

Yaa that's fine now.

Thanks a lot.

[shreeram.maroo]

Is there any code for excel also ?
to round off all the values in excel workbook (every worksheet).

[HansV]

In Excel, you could set the number format to Number with 0 decimal places. This will not round the stored values, only the displayed values.

If you want to round the stored values, what about formulas? We can't just replace the result of a formula with a rounded value. That would remove the formula...

[macropod]

Just a small issue is that it changes the number styles.
My number style includes comma separators and negative values are in bracket(). Like 29,000 or (29,000) - The macro changes it to 29000 or -29000

Here's a more efficient version of Hans' code that preserves whatever formatting your numbers have, rather than imposing a particular format (which is what Hans' updated code does).

Code: Select all

Sub RoundValues()
Application.ScreenUpdating = False
Dim Tbl As Table, Cel As Cell, Rng As Range, Str As String
Application.ScreenUpdating = False
For Each Tbl In ActiveDocument.Tables
  For Each Cel In Tbl.Range.Cells
    Set Rng = Cel.Range
    With Rng
      .End = .End - 1
      If IsNumeric(.Text) Then
        If Not Right(.Text, 1) Like "[0-9]" Then
          Str = Right(.Text, 1)
        Else
          Str = ""
        End If
        .Text = Split(.Text, ".")(0) & Str
      End If
    End With
  Next Cel
Next Tbl
Application.ScreenUpdating = True
End Sub