[Stuart Mattinson]

I am trying to create a formula that will span multipule tables, I am running
a merge option that will create duplicate tables within a word document with
the following formula at the end of each table =sum(b3)+(b4)+(b5).

My problem comes in that I never know how many tables will be created and I
need to add all of these totals together from each table to create a Grand
Total.

What formula would I use to add all the tables together, the cell that I
would need to add in each table is b6

[Doug Robbins - Word MVP]

I would use a macro containing the following code:

Dim i As Long, total As Double
Dim rsum As Range
total = 0
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
MsgBox "The total of all cells B6 is " & total


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
"Stuart Mattinson" Stuart wrote in
message ...
I am trying to create a formula that will span multipule tables, I am
running
a merge option that will create duplicate tables within a word document
with
the following formula at the end of each table =sum(b3)+(b4)+(b5).

My problem comes in that I never know how many tables will be created and
I
need to add all of these totals together from each table to create a Grand
Total.

What formula would I use to add all the tables together, the cell that I
would need to add in each table is b6

[Stuart Mattinson[_2_]]

Dear Doug,

Thanks for the Macro, would it possible to make the macro automatically
place the Words "Grand Total" and the calculated amount after the last table
rather than a message box

As it is a macro should I create a shortcut key for the users to press when
they want to run it as it should only be run after the merge?

"Doug Robbins - Word MVP" wrote:

I would use a macro containing the following code:

Dim i As Long, total As Double
Dim rsum As Range
total = 0
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
MsgBox "The total of all cells B6 is " & total


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
"Stuart Mattinson" Stuart wrote in
message ...
I am trying to create a formula that will span multipule tables, I am
running
a merge option that will create duplicate tables within a word document
with
the following formula at the end of each table =sum(b3)+(b4)+(b5).

My problem comes in that I never know how many tables will be created and
I
need to add all of these totals together from each table to create a Grand
Total.

What formula would I use to add all the tables together, the cell that I
would need to add in each table is b6

[Stuart Mattinson[_2_]]

I was able to add the amount in by adding a bookmark and the following lines

ActiveDocument.Bookmarks("GrandTotal").Select
Selection = "R" & total

I was not able to force the decimal place to be 2 characters only though, so
will keep on wotking on that


"Stuart Mattinson" wrote:

Dear Doug,

Thanks for the Macro, would it possible to make the macro automatically
place the Words "Grand Total" and the calculated amount after the last table
rather than a message box

As it is a macro should I create a shortcut key for the users to press when
they want to run it as it should only be run after the merge?

"Doug Robbins - Word MVP" wrote:

I would use a macro containing the following code:

Dim i As Long, total As Double
Dim rsum As Range
total = 0
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
MsgBox "The total of all cells B6 is " & total


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
"Stuart Mattinson" Stuart wrote in
message ...
I am trying to create a formula that will span multipule tables, I am
running
a merge option that will create duplicate tables within a word document
with
the following formula at the end of each table =sum(b3)+(b4)+(b5).

My problem comes in that I never know how many tables will be created and
I
need to add all of these totals together from each table to create a Grand
Total.

What formula would I use to add all the tables together, the cell that I
would need to add in each table is b6

[Doug Robbins - Word MVP]

Use Format(total, "#,###.00")

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
"Stuart Mattinson" wrote in
message news
I was able to add the amount in by adding a bookmark and the following
lines

ActiveDocument.Bookmarks("GrandTotal").Select
Selection = "R" & total

I was not able to force the decimal place to be 2 characters only though,
so
will keep on wotking on that


"Stuart Mattinson" wrote:

Dear Doug,

Thanks for the Macro, would it possible to make the macro automatically
place the Words "Grand Total" and the calculated amount after the last
table
rather than a message box

As it is a macro should I create a shortcut key for the users to press
when
they want to run it as it should only be run after the merge?

"Doug Robbins - Word MVP" wrote:

I would use a macro containing the following code:

Dim i As Long, total As Double
Dim rsum As Range
total = 0
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
MsgBox "The total of all cells B6 is " & total


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
"Stuart Mattinson" Stuart wrote
in
message ...
I am trying to create a formula that will span multipule tables, I am
running
a merge option that will create duplicate tables within a word
document
with
the following formula at the end of each table =sum(b3)+(b4)+(b5).

My problem comes in that I never know how many tables will be created
and
I
need to add all of these totals together from each table to create a
Grand
Total.

What formula would I use to add all the tables together, the cell
that I
would need to add in each table is b6