#1
|
|||
|
|||
Table Calculations, VBA
Once again, I am trying to perform calculations in a Word Table. I have been struggling with the code for a week now trying multiple things I have found in the forum but I need some expert help.
My table has 6 columns Issues 0 0 0 0 0 (Net Operating Exp)-(Freight-in) 0 0 0 0 0 I have content controls named A,B,C for columns 2,3,4 respectively. Column 5 must be A+B/B and Column 6 must be A+C/C I have attempted using CASE logic but I cannot seem to get it to store the values correctly b/c I get a division by zero error. The code I have is below. Thank you in advance, SuzeG Code:
Option Explicit Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean) Dim i As Long, j As Long, k As Long, l As Long, m As Long Dim oCC As ContentControl With ActiveDocument.Tables(1) If ContentControl.Range.InRange(.Range) Then For i = 2 To .Rows.Count - 3 m = m + 1 Select Case ContentControl.Title Case "A", "B", "C" If Not IsNumeric(ContentControl.Range.Text) Then Cancel = True Beep ContentControl.Range.Select Exit Sub Else ContentControl.Range.Text = FormatValue(ContentControl.Range.Text) End If Set ContentControl = ActiveDocument.SelectContentControlsByTitle("PlanA+B/B").Item(m) With ContentControl .LockContents = False .Range.Text = FormatValue(Plan(ActiveDocument.SelectContentControlsByTitle("A").Item(m).Range.Text, _ ActiveDocument.SelectContentControlsByTitle("B").Item(m).Range.Text, _ ActiveDocument.SelectContentControlsByTitle("C").Item(m).Range.Text)) .LockContents = True End With Set ContentControl = ActiveDocument.SelectContentControlsByTitle("PlanA+C/C").Item(m) With ContentControl .LockContents = False .Range.Text = FormatValue(Plan1(ActiveDocument.SelectContentControlsByTitle("A").Item(m).Range.Text, _ ActiveDocument.SelectContentControlsByTitle("B").Item(m).Range.Text, _ ActiveDocument.SelectContentControlsByTitle("C").Item(m).Range.Text)) .LockContents = True End With End Select Next End If End With End Sub Function Plan(ByRef A As Double, B As Double, C As Double) As Double Plan = (A + B) / B lbl_Exit: Exit Function End Function Function Plan1(ByRef A As Double, B As Double, C As Double) As Double Plan1 = (A + C) / C lbl_Exit: Exit Function End Function Function FormatValue(ByRef pStr As String) As String If InStr(pStr, ".") > 0 Then FormatValue = Format(pStr, "##,####,####,###,##0.0###########;(##,####,####,###,##0.0###########);0") Else FormatValue = Format(pStr, "##,###,###,###,###;(##,###,###,###,###);0") End If lbl_Exit: Exit Function End Function |
#2
|
||||
|
||||
Hi Suze,
This would be much easier to work with if you were to attach a document to a post with the code & table (delete anything sensitive). You do this via the paperclip symbol on the 'Go Advanced' tab. Note: for docm files, you'll need to save them in a zip archive and attach that.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
I have worked on this for a week now. I feel like my head is going to explode..
Attachment included. Thank you so much for any help. |
#4
|
||||
|
||||
Hi Suze,
Maybe I'm missing something here, but when you say: Quote:
"Column 5 must be A+1 and Column 6 must be A+1". After all, mathematically, B/B = 1; and C/C = 1. Of course, if as per your code you meant to say: "Column 5 must be (A+B)/B and Column 6 must be (A+C)/C", then you have a problem if B=0 or C=0. The problem is that you can't divide by 0 (well, you can, but the result is infinite). Please clarify.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Sorry, in my code I do have (A+B)/B and (A+C)/C.
I guess I do not understand how to get the values of each contentcontrol (A,B,C)stored into variables so that I can then perform the calculation. |
#6
|
||||
|
||||
Try the attached.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
That works like a charm but most importantly I can understand the logic and how it works.
Thank you so much for your time and superior help. |
#8
|
||||
|
||||
You're welcome!
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
calculations, tables, vba |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mortgage repayment calculations | BritBiker2 | Excel | 2 | 10-14-2013 10:55 PM |
How to do Calculations In a Text Box? | SteveF | Word | 4 | 09-27-2013 08:34 AM |
Cumulative calculations for two columns in a table to be placed in the footer | noelr | Word VBA | 0 | 09-05-2013 04:18 PM |
Using Reference Tables in calculations | BritBiker2 | Excel | 5 | 02-15-2013 04:19 AM |
Can I do calculations with times? | cubsfan_1 | Excel | 1 | 01-14-2010 06:55 PM |