CODEWITHSUNDEEP
vbams-wordsum
mhammer
mhammer

Reputation: 21

Sum Column in Table to variable

I'm trying to sum up a column of a table in a word document. It's 3 column table, the length depends on the document. What I'm doing in the macro is the following:

I want to sum up the numbers in column 2. Then I'm adding some certain lines (headings in first column, no numbers in column 2) and change the order a bit. But I had the issue that sometimes lines get overwritten (due to bad programming...), so I want to sum up the second column in the end and compare the result with the one before (like a checksum).

How can I implement to sum up the values of the second table to a variable (not writing it into a cell), and compare it in the end?

Sub ModuleTest()

Dim numrows As Long
Dim col2 As Range

numrows = ActiveDocument.Tables(1).Rows.Count

Set col2 = ActiveDocument.Range( _
    Start:=ActiveDocument.Tables(1).Cell(1, 2).Range.Start, _
    End:=ActiveDocument.Tables(1).Cell(numrows, 2).Range.End)

'Code to sum up values in range col2

End Sub

I managed to define the range but now i want to sum it up to a variable.

Upvotes: 2

Views: 2471

Answers (1)

Cindy Meister
Cindy Meister

Reputation: 25663

Good start and logical thinking - the only problem being that in Word a Range object must be contiguous (left-to-right, top-to-bottom). And the cells in a table column are not contiguous, so (unfortunately for us) cannot be assigned to a Range object.

That means you can either loop the Cell objects in a Column object or you can loop the Cells in a Selection object. The first is more correct from the object-oriented point of view; the second is faster in execution if there are a lot of cells.

Both approaches are in the code that follows. It tests whether the (truncated) cell content is numeric or not and sums those that are. (Why truncated: because the last characters of a Word cell are Chr(13) & Chr(7), which aren't numeric.)

Sub TableColumn()

'Working with Selection is faster
'    Dim tbl As word.Table
'    Dim runningSum As Double
'    Dim sel As word.Selection
'    Dim cel As word.Cell
'    Dim celContent As String
'
'    Set tbl = ActiveDocument.Tables(1)
'    runningSum = 0
'    tbl.Cell(1, 2).Select
'    Set sel = Selection
'    sel.Columns(1).Select
'
'    For Each cel In sel.Cells
'        celContent = Mid(cel.Range.Text, 1, Len(cel.Range.Text) - 1)
'        If IsNumeric(celContent) Then
'            runningSum = runningSum + CDbl(celContent)
'        End If
'    Next
'    Debug.Print runningSum

'Working with the pure object model is slower, but "cleaner"
    Dim tbl As word.Table
    Dim col As word.Column
    Dim runningSum As Double
    Dim cel As word.Cell
    Dim celContent As String

    Set tbl = ActiveDocument.Tables(1)
    Set col = tbl.Columns(2)
    runningSum = 0

    For Each cel In col.Cells
        celContent = Mid(cel.Range.Text, 1, Len(cel.Range.Text) - 1)
        If IsNumeric(celContent) Then
            runningSum = runningSum + CDbl(celContent)
        End If
    Next
    Debug.Print runningSum
End Sub

Upvotes: 1

Related Questions