Sum validation in ms word..

macropod · #6 04-18-2020, 02:27 PM

Try the following macro. It loops through all tables in the document, testing whether cells in anything other than the top row & left column are numeric, highlighting them if they aren't. If just one cell other than the total in a column exhibits an error, a new value is calculated. Pink highlight is used for non-numeric entries, green for repaired entries and yellow for totals where a repair can't be made. That should do most of the work for you, leaving you with relatively few things to check.

Code:

Sub Demo()
Dim Tbl As Table, r As Long, c As Long, x As Long, y As Long, Str As String, Val As Double
For Each Tbl In ActiveDocument.Tables
  With Tbl
    For c = 2 To .Columns.Count
      Val = 0: x = 0
      For r = 2 To .Rows.Count - 1
        With .Cell(r, c).Range
          Str = Trim(Split(.Text, vbCr)(0))
          .Text = Str
          If IsNumeric(Str) Then
            Val = Val + CDbl(Str)
          Else
            .HighlightColorIndex = wdPink
            x = x + 1: y = r
          End If
        End With
      Next
      With .Cell(r + 1, c).Range
        Str = Trim(Split(.Text, vbCr)(0))
        .Text = Str
        If IsNumeric(Str) Then
          If Val <> CDbl(Str) Then
            If x = 1 Then
              With Tbl.Cell(y, c).Range
                .Text = Format(CDbl(Str) - Val, "#,##0.00")
                .HighlightColorIndex = wdBrightGreen
              End With
            Else
              .HighlightColorIndex = wdYellow
            End If
          End If
        Else
          If Val <> 0 Then .HighlightColorIndex = wdYellow
        End If
      End With
    Next
  End With
Next
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Abbreviations Validation - Word	mrsandes	Word VBA	17	04-16-2020 02:40 PM
Data Validation in Microsoft Word Template	BradleyCase	Word VBA	1	07-16-2019 12:37 PM
Automate Job application word doc to Excel (with data validation)	dylansmith	Office	1	02-11-2018 12:58 PM
validation email address in word text filed	sameerahmad_P	Word VBA	1	03-07-2014 02:59 PM
Forms -with validation	ubns	Excel	1	05-04-2012 08:51 AM