Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old 12-13-2013, 07:23 AM
SuzeG SuzeG is offline Table Calculations, VBA Windows 7 64bit Table Calculations, VBA Office 2010 64bit
Novice
Table Calculations, VBA
 
Join Date: Dec 2013
Location: Va Beach
Posts: 21
SuzeG is on a distinguished road
Red face 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
Reply With Quote
 

Tags
calculations, tables, vba

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Table Calculations, VBA 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
Table Calculations, VBA Using Reference Tables in calculations BritBiker2 Excel 5 02-15-2013 04:19 AM
Table Calculations, VBA Can I do calculations with times? cubsfan_1 Excel 1 01-14-2010 06:55 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:54 AM.


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