![]() |
|
#1
|
|||
|
|||
|
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
|
| Tags |
| calculations, tables, vba |
| Thread Tools | |
| Display Modes | |
|
|
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 |