#1
|
|||
|
|||
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 .. |
#2
|
|||
|
|||
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.
|
#3
|
|||
|
|||
Thanks for immediate response..
Inserting a excel table and using excel function also be helpful for me.... |
#4
|
|||
|
|||
Word's table math is very rudimentary compared to Excel. It is very finicky, too.
|
#5
|
|||
|
|||
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. |
#6
|
||||
|
||||
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] |
#7
|
||||
|
||||
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] |
#8
|
|||
|
|||
Points made. Go ahead.
|
#9
|
|||
|
|||
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
|
#10
|
||||
|
||||
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] |
#11
|
|||
|
|||
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.... |
#12
|
||||
|
||||
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] |
#13
|
|||
|
|||
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… |
#14
|
||||
|
||||
Quote:
Quote:
Quote:
Quote:
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 Code:
.HighlightColorIndex = wdYellow End If
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
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 |