Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Portland, Maine, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Boston, Massachusetts, USA
    Posts
    389
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Portland, Maine, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •