# Thread: Table Math (Word XP/SP2)

1. ## Table Math (Word XP/SP2)

I have a client that uses tables in a Word template (they don't want to insert an Excel sheet) that calculates some totals. Column D is number of items. Col E is price. Col F is total based on D*E. They have a macro that inserts the formula based on the current row number. (See below). However, if a row is inserted or deleted in the table - everything below the insertion or deletion is then wrong when updated! Is there any way to make this work?

intRow = Selection.Information(wdEndOfRangeRowNumber)
strFormula = "=d" & intRow & "*e" & intRow
Selection.InsertFormula Formula:=strFormula, NumberFormat:="\$#,##0.00;(\$#,##0.00)"
Selection.MoveDown Unit:=wdLine, Count:=1

2. ## Re: Table Math (Word XP/SP2)

Word tables are not really designed for this kind of thing. What is the objection against inserting an Excel sheet?

3. ## Re: Table Math (Word XP/SP2)

<P ID="edit" class=small>(Edited by Andrew77 on 29-Jun-04 22:55. Changed "E" to "F" in text (not code).)</P>You're inserting formula fields into column E. These fields, once inserted, contain hard references to the cells they're using for their calculations. For example, in the second row of the table, the last cell field would look like:

=d2*e2

When you insert a row above the second row, this field is now in the third row, but is still taking values from cells in the second.

As Hans said, Word tables aren't really the right tool for the job here, but if you really want to use them, you'll need to replace the contents of each cell in column F every time you insert/delete a row.

Try the following:

<pre>Sub UpdateColumnF()
Dim tbl As Table
Set tbl = Selection.Tables(1)
Dim r As Row
Dim i As Integer
For i = 2 To tbl.Rows.Count
Set r = tbl.Rows(i)
r.Cells(6).Select
Selection.Delete
Selection.InsertFormula Formula:="=d" & i & "*e" & i, _
NumberFormat:="\$#,##0.00;(\$#,##0.00)"
Next i
End Sub
</pre>

4. ## Re: Table Math (Word XP/SP2)

Another way to keep the formulae below the inserted row working is use construct the formulae so that they use relative row addressing. See <post#=365442>post 365442</post#> for some examples of this. A caveat, though: the newly inserted row would need to contain a formula that uses the same relative addressing scheme. Since your macro seems to be inserting the same formula that is used in the rows above/below, you might find it easiest to simply copy down the formula from the row above.

Cheers

5. ## Re: Table Math (Word XP/SP2)

If you're client insists on using a Word table, then you should read the method described by Dave Rado, MVP entitled How to get a formula field to total an entire table column, even if some cells in the column contain text or are blank
Cheers,

6. ## Re: Table Math (Word XP/SP2)

That is an interesting approach. Thank you.

What I have done for now is to unlink all of the formulas as they are input - especially the formulas which went across (col d row x * col e row x) - so they don't get updated incorrectly when rows are added or deleted. If numbers change in col d or e, instead or updating the field, they re-run the macro to put the formula in and unlink it again.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•