Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-17-2020, 10:13 AM
nmkhan3010 nmkhan3010 is offline Sum validation in ms word.. Windows 10 Sum validation in ms word.. Office 2016
Novice
Sum validation in ms word..
 
Join Date: Feb 2020
Posts: 23
nmkhan3010 is on a distinguished road
Default Sum validation in ms word..

Hi,

Am having a multiple tables in a document and i need to validate the sum of each tables in a document manually and it is taking more time and effecting productivity, is there any macro can help me in this regards and am new to the VBA and basic knowledge about this...

Am using excel fot this work to copy the table and calculate sum at the end of the table and apply conditional formatting highlight duplicate values and manlly work on not highlighted values, it is taking more time to do in excel and then again making corrections in word document.

My requirement : format is ".rtf"

Every table last row is predefined total row...

A new row to be added at the end of the table and calculate sum and if it is matches to the above total row it should be in highlighted in green color and if not match red color, then my manully intervention only on the red rows .....

I dont know may be it is possible or not just trying...

any help or suggestions is highly appreciated ....

Please find attachment for examples ...

Thanks ..
Attached Files
File Type: docx test.docx (56.0 KB, 7 views)
Reply With Quote
  #2  
Old 04-17-2020, 10:31 AM
Charles Kenyon Charles Kenyon is offline Sum validation in ms word.. Windows 10 Sum validation in ms word.. Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,083
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
Default

Consider inserting an Excel table into your Word document and using Excel functions for your validation. Unless you need it to be a Word table, you can do this.
Reply With Quote
  #3  
Old 04-17-2020, 10:34 AM
nmkhan3010 nmkhan3010 is offline Sum validation in ms word.. Windows 10 Sum validation in ms word.. Office 2016
Novice
Sum validation in ms word..
 
Join Date: Feb 2020
Posts: 23
nmkhan3010 is on a distinguished road
Default

Thanks for immediate response..

Inserting a excel table and using excel function also be helpful for me....
Reply With Quote
  #4  
Old 04-17-2020, 11:15 AM
Charles Kenyon Charles Kenyon is offline Sum validation in ms word.. Windows 10 Sum validation in ms word.. Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,083
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
Default

Word's table math is very rudimentary compared to Excel. It is very finicky, too.
Reply With Quote
  #5  
Old 04-17-2020, 11:29 AM
nmkhan3010 nmkhan3010 is offline Sum validation in ms word.. Windows 10 Sum validation in ms word.. Office 2016
Novice
Sum validation in ms word..
 
Join Date: Feb 2020
Posts: 23
nmkhan3010 is on a distinguished road
Default

Thanks for the suggestion can we do in excel vba macro for this ...

like calcualting a sum at the end of the table excluding the above total row and validating the sum by highlighting the match case and didnt match case.
Reply With Quote
  #6  
Old 04-17-2020, 03:16 PM
macropod's Avatar
macropod macropod is offline Sum validation in ms word.. Windows 7 64bit Sum validation in ms word.. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Do all tables have the same structure (i.e. a single heading row at the top, a single totals row at the bottom, and single description column at the side, with only numeric data in the other cells)?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 04-17-2020, 03:19 PM
macropod's Avatar
macropod macropod is offline Sum validation in ms word.. Windows 7 64bit Sum validation in ms word.. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Charles Kenyon View Post
Word's table math is very rudimentary compared to Excel. It is very finicky, too.
Simple summations in Word - which is what the OP seeks - are hardly finicky. Besides which a VBA solution is sought. The overheads of converting each table to Excel for what the OP seeks greatly outweigh any benefits that might be gained.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 04-17-2020, 06:17 PM
Charles Kenyon Charles Kenyon is offline Sum validation in ms word.. Windows 10 Sum validation in ms word.. Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,083
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
Default

Points made. Go ahead.
Reply With Quote
  #9  
Old 04-18-2020, 11:03 AM
nmkhan3010 nmkhan3010 is offline Sum validation in ms word.. Windows 10 Sum validation in ms word.. Office 2016
Novice
Sum validation in ms word..
 
Join Date: Feb 2020
Posts: 23
nmkhan3010 is on a distinguished road
Default

All the tables will not have the same structres top row is the headings and last is the total for all the tables in a document and tables row and columns are vary from table to table and all the tables will have numbers only and denominated by "," "." and vice-versa
Reply With Quote
  #10  
Old 04-18-2020, 02:27 PM
macropod's Avatar
macropod macropod is offline Sum validation in ms word.. Windows 7 64bit Sum validation in ms word.. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #11  
Old 04-19-2020, 08:32 AM
nmkhan3010 nmkhan3010 is offline Sum validation in ms word.. Windows 10 Sum validation in ms word.. Office 2016
Novice
Sum validation in ms word..
 
Join Date: Feb 2020
Posts: 23
nmkhan3010 is on a distinguished road
Default

I had checked the code running successfully and highlighting pink color, green color exactly works but for yellow color - totals where a repair can't be made means where a total is mismatched somewhere and can’t find where it happened I hope so and it is highlighting yellow color where any action is not required, and all the above segments are very clear and whereas sum of that above items is exactly matched with the total while doing manually with excel but it is getting highlighted while running a macro for that segments.

Can you please confirm or review once and excuse me if am anything wrong...

Thank you so much....
Reply With Quote
  #12  
Old 04-19-2020, 01:54 PM
macropod's Avatar
macropod macropod is offline Sum validation in ms word.. Windows 7 64bit Sum validation in ms word.. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Yellow highlight indicates action is required, but the code can't tell where. Usually, though, there would be at least two rows with pink highlight in the same column.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #13  
Old 04-20-2020, 10:23 AM
nmkhan3010 nmkhan3010 is offline Sum validation in ms word.. Windows 10 Sum validation in ms word.. Office 2016
Novice
Sum validation in ms word..
 
Join Date: Feb 2020
Posts: 23
nmkhan3010 is on a distinguished road
Default

2,188,431,699.45 1,512,333,332.14
48,728,346.87 21,138,042.40
2,237,160,046.32 1,533,471,374.54

I have tested the above values but it is still highlighting yellow even any action is not required for the above segments.

Above macro was running all the tables and document was stacked and mostly document was not responding error I realized it is due to more no of pages and tables in different-different formats , now I want to check the tables individually by using the macro or modify the previous macro because this will serve my purpose exactly…

Tables are in same format top row is heading and last row is the total (other than the top row & left column are numeric).

Macro has to be run on the selected table only or any area or any cell in that table only and not on all the tables in a document.

Pink highlight indicates for non-numeric entries (#$%^&)
Green highlight indicates for repaired entries (it is repairing only when the number is with characters or special characters only 12&5 , 136%^)
Yellow highlight indicates action is required, but the code cannot tell where it exists (above code was highlighting even any action is not required also)

Newly added:
Grey highlight indicates if all the above segments are exactly matched with the total or the last row and does not required any action on that column.

Newly added:
Green highlight indicates for repaired entries – only when one value is wrong in that column and get rectify suppose instead of 8 it is given as 3 - can we repaired this type of entries by matching with the total or same as repairing with character or special characters previously and suppose if there is more than two wrong numeric entries then it will comes in yellow color (I think it’s possible only one cell value is wrong in that column I hope so)

Am sorry, if any inconvenience caused to you, am get all this clarifications only by working on the previous macro and that’s was a great and I didn’t expect that will be possible but you have make it possible and am so thankful to you….

Am really appreciating and heart fully thanks for your solutions and prompt responses…
Reply With Quote
  #14  
Old 04-20-2020, 02:14 PM
macropod's Avatar
macropod macropod is offline Sum validation in ms word.. Windows 7 64bit Sum validation in ms word.. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by nmkhan3010 View Post
Above macro was running all the tables and document was stacked and mostly document was not responding error I realized it is due to more no of pages and tables in different-different formats
Well, you did tell me that:
Quote:
Originally Posted by nmkhan3010 View Post
All the tables will not have the same structres top row is the headings and last is the total for all the tables in a document and tables row and columns are vary from table to table and all the tables will have numbers only and denominated by "," "." and vice-versa
You can't expect the macro to work correctly if the tables have different formats from what you said.

Quote:
Originally Posted by nmkhan3010 View Post
Pink highlight indicates for non-numeric entries (#$%^&)
Green highlight indicates for repaired entries (it is repairing only when the number is with characters or special characters only 12&5 , 136%^)
Those are not the only conditions the macro would count as non-numeric, and the presence of $ or % does not necessarily mean the contents are non-numeric. $1,000.00 is numeric, as is 0.99%.
Quote:
Originally Posted by nmkhan3010 View Post
Newly added:
Green highlight indicates for repaired entries – only when one value is wrong in that column and get rectify suppose instead of 8 it is given as 3 - can we repaired this type of entries by matching with the total or same as repairing with character or special characters previously and suppose if there is more than two wrong numeric entries then it will comes in yellow color (I think it’s possible only one cell value is wrong in that column I hope so
That is not possible - only non-numeric entries can be repaired. It is impossible for the code to know that an 8 in a numeric entry should be a 3, or vice-versa.

As for the code -
Change:
For Each Tbl In ActiveDocument.Tables
to:
For Each Tbl In Selection.Tables

Change:
If Val <> CDbl(Str) Then
to:
If Format(Val, "0.00") <> Format(CDbl(Str), "0.00") Then

Insert:
Code:
          Else
            .HighlightColorIndex = wdGray25
after:
Code:
              .HighlightColorIndex = wdYellow
            End If
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sum validation in ms word.. 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
Sum validation in ms word.. 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

Other Forums: Access Forums

All times are GMT -7. The time now is 12:47 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft