Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #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
  #2  
Old 12-13-2013, 04:05 PM
macropod's Avatar
macropod macropod is offline Table Calculations, VBA Windows 7 32bit Table Calculations, VBA Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
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

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]
Reply With Quote
  #3  
Old 12-14-2013, 06:37 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
Default

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.
Attached Files
File Type: zip TYPlanLY.zip (32.6 KB, 18 views)
Reply With Quote
  #4  
Old 12-14-2013, 01:40 PM
macropod's Avatar
macropod macropod is offline Table Calculations, VBA Windows 7 32bit Table Calculations, VBA Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
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

Hi Suze,

Maybe I'm missing something here, but when you say:
Quote:
Column 5 must be A+B/B and Column 6 must be A+C/C
that seems to me to be the same as"
"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]
Reply With Quote
  #5  
Old 12-14-2013, 05:21 PM
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
Default

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.
Reply With Quote
  #6  
Old 12-14-2013, 10:20 PM
macropod's Avatar
macropod macropod is offline Table Calculations, VBA Windows 7 32bit Table Calculations, VBA Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
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 attached.
Attached Files
File Type: zip SuzeG.zip (37.8 KB, 36 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 12-15-2013, 07:11 PM
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
Default

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.
Reply With Quote
  #8  
Old 12-15-2013, 07:12 PM
macropod's Avatar
macropod macropod is offline Table Calculations, VBA Windows 7 32bit Table Calculations, VBA Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
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

You're welcome!
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Tags
calculations, tables, vba



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 06:19 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