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
Rounding off
-
- 2StarLounger
- Posts: 183
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
-
- Administrator
- Posts: 78930
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Rounding off
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
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 183
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Rounding off
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
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
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Rounding off
This is a guess, but try changing this line:
to this:
Code: Select all
cel.Range.Text = Round(s, 0)
Code: Select all
cel.Range.Text = Format(Round(s, 0), "#,##0;(#,##0)")
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 2StarLounger
- Posts: 183
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Rounding off
Yaa that's fine now.
Thanks a lot.
Thanks a lot.
-
- 2StarLounger
- Posts: 183
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Rounding off
Is there any code for excel also ?
to round off all the values in excel workbook (every worksheet).
to round off all the values in excel workbook (every worksheet).
-
- Administrator
- Posts: 78930
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Rounding off
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...
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...
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 508
- Joined: 17 Dec 2010, 03:14
Re: Rounding off
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).shreeram.maroo wrote: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
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
Paul Edstein
[Fmr MS MVP - Word]
[Fmr MS MVP - Word]