0

I've got a bunch of tables and the last digit of all the numbers need to be replaced with zero and I'm not able to figure out the code to make it work. I have numbers ranging from 1 to 20,000 so the comma will have to be taken into consideration. The closest I'm able to get is having a zero added to the very end, not replacing the last.

So the columns should go from this: 3,271 1,559 1,712 12 279

To this: 3,270 1,550 1,710 10 270

2 Answers 2

0

Try this ... it might have issues if you have merged cells, but it is a starting point for you. It also assumes because you didn't say otherwise that the table cell content it's searching for is numeric. If there are cells with mixed alpha-numeric data it won't find them.

Sub ChangeLastToZero()
    Dim doc As Word.Document, rng As Word.Range
    Dim tbl As Word.Table, r As Long, c As Long
    Set doc = ActiveDocument
    
    For Each tbl In doc.Tables
        For r = 1 To tbl.rows.Count
            For c = 1 To tbl.rows(r).Cells.Count
                Set rng = tbl.rows(r).Cells(c).Range
                Do While Asc(rng.Characters.Last) = 13
                    rng.MoveEnd Word.WdUnits.wdCharacter, Count:=-1
                Loop
                If IsNumeric(rng.Text) Then
                    rng.Characters.Last = 0
                End If
            Next
        Next
    Next
End Sub
2
  • The cells are merged, yeah
    – Steven
    Jan 28, 2021 at 1:42
  • The problem will be if they are vertically merged. For example, Cell 2 in Row 1 is vertically merged with Cell 2 in Row 2. If cells are horizontally merged in the same row, no problem. If there are both kinds ... oh well, heavy sigh ... what do you have? Jan 28, 2021 at 2:31
0

It seems that you are rounding every number down. You could divide each number by 10, get the integer part, and multiply it by 10 again.

Dim numTxt As String
Dim num As Long
Dim result As Long
Dim resultTxt As String

You set numTxt as each number in your tables, in string format.

Then, convert number numTxt from String to Long

num = CLng(numTxt)

Then, divide it by 10, round it down, and multiply by 10 again.

result = CLng(num / 10)*10

Finally, format it again.

resultTxt = Format(result, "#,##0")

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .