Multipule Tables - Grand Total Formula

[Stuart Mattinson]

I am trying to add the total of multipule tables together, I have the formula
below that I have added to the VBA of "This Document" so that when the
document opens the macro will run.

I have two questions :

1. Where to I place the Format(total, "#,###.00") so that I have the correct
deimal placing in the final total

2. Because I am running the macro every time the document opens I can end up
with the answer multipule times, I could ask the users to run it manually but
sometimes they forget and then the document will be mailed out without a
Grand Total. I have reied to add a Delete before the marco starts but it is
not working 100%

My Current Macro is :

Private Sub Document_Open()
ActiveDocument.Bookmarks("GrandTotal").Select
Selection.Delete
Dim i As Long, total As Double
Dim rsum As Range
total = Format(total, "#,###.00")
With ActiveDocument
For i = 1 To .Tables.Count
Set rsum = .Tables(i).Cell(6, 2).Range
rsum.End = rsum.End - 1
If IsNumeric(rsum.Text) Then
total = total + rsum.Text
End If
Next i
End With
ActiveDocument.Bookmarks("GrandTotal").Select
Selection = "R" & total
End Sub

 

[Graham Mayor]

Your macro appears to delete the bookmark you are trying to select later in
the macro.
I take it that you are trying to get the bookmark to hold the result of the
calculation? In that case

Private Sub Document_Open()
Dim i As Long, total As Double
Dim rSum As Range
Dim rBMark As Range
Set rBMark = ActiveDocument.Bookmarks("GrandTotal").Range
With ActiveDocument
For i = 1 To .Tables.Count
Set rSum = .Tables(i).Cell(6, 2).Range
rSum.End = rSum.End - 1
If IsNumeric(rSum.Text) Then
total = total + rSum.Text
End If
Next i
End With
rBMark.Text = "R" & Format(total, "#,###.00")
With ActiveDocument
.Bookmarks.Add "GrandTotal", rBMark
.Fields.Update
End With
End Sub


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>