Alternatives to Sum(Above)

[solex]

The sum(above) function is flakey to say the least has anyone written a
replacment vba macro to perfor the same function?

Thanks,
Dan

 

[Suzanne S. Barnhill]

See http://word.mvps.org/FAQs/AppErrors/SumAboveIncorrect.htm

 

[solex]

Alot of people point to that page but it would not solve my problem. I am
using word as a reporting template to output access data. The user does not
want to interact with word and any point except the initial design of the
document.

My solution was to create a new tag to represent the number of rows in the
sum formula and then subsitute that tag with number of rows after the data
has been inserted. Apparently the SUM(A1:A50) does not exhibit the same
problems as SUM(ABOVE).

Thanks,
Dan

Example of the field with the new tag:
{ = SUM(B3:B<ROWS />) \# "#,##0;(#,##0)" }


I then call this routine to update the formulas in the document.

Public Sub FieldTagSubsitution(ByRef doc As Word.Document, _
ByVal Tag As String, ByVal ReplaceWith As String)

' Looks through the fields for the specified tag and
' replaces the tag with the passed in value then updates the fields.

Dim fld As Word.Field
Dim code As String

For Each fld In doc.Fields
code = fld.code.Text
If InStr(code, Tag) > 0 Then
fld.code.Text = Replace(code, Tag, ReplaceWith)
fld.Update
End If
Next

Set fld = Nothing
End Sub

 

[macropod]

Hi Dan,

The two-table solution mentioned at:
http://word.mvps.org/FAQs/TblsFldsFms/TotalColumn.htm
(url all one line)
would seem ideally suited to your situation.

For more information on the limitations of SUM(ABOVE) etc, and solutions to
various field math problems, check out:
http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=wrd&Number=365442
(url all one line)

Cheers

 

[solex]

Thank you, both documents have some good advice.

Hi Dan,

The two-table solution mentioned at:
http://word.mvps.org/FAQs/TblsFldsFms/TotalColumn.htm
(url all one line)
would seem ideally suited to your situation.

For more information on the limitations of SUM(ABOVE) etc, and solutions to
various field math problems, check out:
http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=wrd&Number=365442
(url all one line)

Cheers


written