CODEWITHSUNDEEP
vbams-word
Mapleleaf
Mapleleaf

Reputation: 1

Round Numbers In Table Column Word VBA

Trying to only round the numbers in column 4 but this macro code isn't working properly. Maybe there is another way to word it.

It is using a selection for the range but I think there has to be another way of putting this.

Thank you.

Sub RoundNumbersInColumn()

    Dim rowMax As Long
    Dim rowStart As Long
    Dim colNo As Long
    Dim aCell As Cell
    Dim aTable As Table
    Dim tabPos As Single
    Dim k As Long
    Dim aValue As Single
    Dim formatStr As String
    Dim s As String
    
    ' set format to number of decimal places to round to
    formatStr = "#0.0000"
    
    Set aTable = Selection.Tables(1)
        rowMax = aTable.Rows.Count
    Set aCell = Selection.Cells(1)
        rowStart = aCell.RowIndex
        colNo = aCell.ColumnIndex
        tabPos = -1
        
    If aCell.Range.ParagraphFormat.TabStops.Count > 0 Then
    
        If aCell.Range.ParagraphFormat.TabStops.Item(1).Alignment = wdAlignTabDecimal Then _
            tabPos = aCell.Range.ParagraphFormat.TabStops.Item(1).Position
        End If

    For k = rowStart To rowMax
    
        Set aCell = aTable.Cell(Row:=k, Column:=4)
            s = aCell.Range.Text
            s = Left(s, Len(s) - 1)
        
        If IsNumeric(s) Then
        
            aValue = Val(s)
            
            With aCell.Range.ParagraphFormat.TabStops
                .ClearAll
                .Add Position:=tabPos, Alignment:=wdAlignParagraphCenter
            End With
            
            aCell.Range.Text = Format(Str(aValue), formatStr)
            
        End If
    Next k

End Sub

Upvotes: 0

Views: 249

Answers (1)

John Korchok
John Korchok

Reputation: 4913

Remove the underline after Then in line 26. Then add an End If to close the first If statement. Like so:

If aCell.Range.ParagraphFormat.TabStops.Count > 0 Then
    If aCell.Range.ParagraphFormat.TabStops.item(1).Alignment = wdAlignTabDecimal Then
        tabPos = aCell.Range.ParagraphFormat.TabStops.item(1).Position
    End If
End If

Upvotes: 1

Related Questions