Alternatives to Sum(Above)

S

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
 
S

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
 
S

solex

Top