Skip to content

Instantly share code, notes, and snippets.

@asbjornu
Created November 29, 2012 15:20
Show Gist options
  • Save asbjornu/4169749 to your computer and use it in GitHub Desktop.
Save asbjornu/4169749 to your computer and use it in GitHub Desktop.
Sums Cell(j, k) in all Tables in a Microsoft Word document and places the Sum in the last Table in the document
'
' Sums Cell(j, k) in all Tables in a Microsoft Word document and places the Sum in the last Table in the document.
'
' It will only work for very specific scenarios where your Tables follow the standard Word Table template where the
' first row and cell contains headers and the last row contains some sort of footer. The rest of the cells in
' the Table will be summarized according to the rowCount, rowOffset, cellCount and cellOffset constants below.
'
Sub Sum()
' This constant is the string of the first cell of the first row in the tables
' it needs to find to add its cells to the total sum
Const tableHeader As String = "WHATEVER TO LOOK FOR IN THE FIRST COLUMN OF THE FIRST ROW OF THE TABLES YOU WANT TO SUMMARIZE"
' This is the number of rows in your tables you're interested in summarizing.
' If you have a table with 5 rows where the 1st is a header and the 5th is a
' summary (footer), set this to 3.
Const rowCount As Integer = 3
' This is the number of the first row in the table you're interested in. If you
' have a header row (row 1), this should be set to 2.
Const rowOffset As Integer = 2
' This is the number of cells in your tables you're interested in summarizing.
' If you have a table with 3 cells, where the 1st is a row header, and the last
' two are what you're interested in summarizing, set this to 2.
Const cellCount As Integer = 2
' This is the number of the first cell in the table you're interested in. IF
' you have a row header as the 1st cell, and the 2nd cell contains interesting
' data you want to summarize, set this to 2.
Const cellOffset As Integer = 2
Dim sums(rowCount, cellCount) As String
Dim table As table
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim tableCount As Integer
Dim text As String
Dim value As Integer
Dim Sum As String
Dim formula As String
Dim row As Integer
Dim cell As Integer
Dim header As String
Dim currentCell As cell
tableCount = ActiveDocument.Tables.count
For i = 1 To tableCount
Set table = ActiveDocument.Tables(i)
header = table.cell(1, 1).Range.text
If InStr(header, tableHeader) Then
For j = 0 To rowCount - 1
For k = 0 To cellCount - 1
row = j + rowOffset
cell = k + cellOffset
Set currentCell = table.cell(row, cell)
text = currentCell.Range.text
value = Val(text)
Sum = sums(j, k)
If i = ActiveDocument.Tables.count Then
' We're in the last table, so let's sum it!
formula = "=SUM(" & Sum & ")"
currentCell.Range.text = ""
currentCell.formula (formula)
ElseIf Sum = "" Then
sums(j, k) = value
Else
sums(j, k) = Sum & ";" & value
End If
Next
Next
Else
Debug.Print "Skipping Table " & i & " because it doesn't contain the word '" & tableHeader & "' in Cell(1, 1)"
End If
Next
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment