Calculating and formatting in a table

J

JayM

I have a word document (invoice). I have written some code (it is probably
very crude code but it works - well sort of).

the user completes the sections of the table with the figures they require
costs, disbursements, disbursements inc vat etc. They then click on a macro
button which runs a macro that will calculate the vat that is required and
add it all together less any costs already paid and also formats the figures
to #,###,##0.00.

My problem occurs when the user then changes and figure and recalculates. A
figure of 13,776.00 becomes 13.00 it ignores the figures aftet the first
comma. I have pasted my code and would be grateful if anyone could see where
I have gone wrong or point me in a better direction.

Many thanks

JayM

Code
Sub TotalTableCellValues()
Dim cTable2B2 As Cell
Dim cTable2B3 As Cell
Dim cTable2B4 As Cell
Dim cTable2B5 As Cell
Dim cTable2B6 As Cell
Dim cTable2B7 As Cell
Dim cTable2B8 As Cell
Dim cTable2B9 As Cell
Dim cTable2B10 As Cell
Dim cTable2B11 As Cell
Dim cTable2B12 As Cell
Dim cSubTotalB6 As Cell
Dim cTotalB12 As Cell
Dim cSubTotalonVatB7 As Cell
Dim Fees As Currency
Dim Tfees As Currency
Dim Taxpaid As Currency
Dim Taxunpaid As Currency
Dim iSubTotal As Currency
Dim iVatonSubTotal As Currency
Dim Paid As Currency
Dim Unpaid As Currency
Dim iSubTotalincVat As Currency
Dim LessPaid As Currency
Dim Total As Currency


Set table2B2 = ActiveDocument.Tables(2).Cell(2, 2)
Set table2B3 = ActiveDocument.Tables(2).Cell(3, 2)
Set table2B4 = ActiveDocument.Tables(2).Cell(4, 2)
Set table2B5 = ActiveDocument.Tables(2).Cell(5, 2)
Set cSubTotalB6 = ActiveDocument.Tables(2).Cell(6, 2)
Set cVatonSubTotalB7 = ActiveDocument.Tables(2).Cell(7, 2)
Set table2B8 = ActiveDocument.Tables(2).Cell(8, 2)
Set table2B9 = ActiveDocument.Tables(2).Cell(9, 2)
Set table2B10 = ActiveDocument.Tables(2).Cell(10, 2)
Set table2B11 = ActiveDocument.Tables(2).Cell(11, 2)
Set cTotalB12 = ActiveDocument.Tables(2).Cell(12, 2)

iSubTotal = Val(table2B2.Range.Text) + Val(table2B3.Range.Text) +
Val(table2B4.Range.Text) + Val(table2B5.Range.Text)

cSubTotalB6.Range.Text = iSubTotal

cVatonSubTotalB7.Range.Text = iVatonSubTotal

iVatonSubTotal = Val(iSubTotal) * 17.5 / 100

iSubTotalincVat = Val(iSubTotal) + Val(iVatonSubTotal) +
Val(table2B8.Range.Text) + Val(table2B9.Range.Text)

Total = Val(iSubTotalincVat) - Val(table2B11.Range.Text)

cTotalB12.Range.Text = Total
Fees = Val(table2B2.Range.Text)
Tfees = Val(table2B3.Range.Text)
Taxpaid = Val(table2B4.Range.Text)
Taxunpaid = Val(table2B5.Range.Text)
Paid = Val(table2B8.Range.Text)
Unpaid = Val(table2B9.Range.Text)
LessPaid = Val(table2B11.Range.Text)

With ActiveDocument.Tables(2)
.Cell(2, 2).Range.Text = Format(Fees, "#,###,##0.00")
.Cell(3, 2).Range.Text = Format(Tfees, "#,###,##0.00")
.Cell(4, 2).Range.Text = Format(Taxpaid, "#,###,##0.00")
.Cell(5, 2).Range.Text = Format(Taxunpaid, "#,###,##0.00")
.Cell(6, 2).Range.Text = Format(iSubTotal, "#,###,##0.00")
.Cell(7, 2).Range.Text = Format(iVatonSubTotal, "#,###,##0.00")
.Cell(8, 2).Range.Text = Format(Paid, "#,###,##0.00")
.Cell(9, 2).Range.Text = Format(Unpaid, "#,###,##0.00")
.Cell(10, 2).Range.Text = Format(iSubTotalincVat, "#,###,##0.00")
.Cell(11, 2).Range.Text = Format(LessPaid, "#,###,##0.00")
.Cell(12, 2).Range.Text = Format(Total, "£" & "#,###,##0.00")

End With
End Sub
 
J

Jezebel

In place of the Val() statements, write your own function to extract the
value from string: something like

iSubTotal = GetVal(table2B2.Range.Text) + ....

Private Function GetVal(NumString as String) as currency
GetVal = CCur(Replace$(Numstring, ",", ""))
End Function


Simpler still would be to embed an Excel table within the document. You can
format at so it looks exactly the same; and you wouldn't need any code at
all.
 
M

macropod

Hi Jay,

You're making this harder than it needs to be.

Instead of using vba, you could simply set each formfield's properties to
'calculate on exit' and insert formula fields wherever you need the to
calculate and format the results.

For example, say you have two formfields that set the bookmarks 'Fees' and
'TaxRate' and you want to multiply these to get the amount of tax. In that
case, a formula field coded as {=Fees*TaxRate \# £,0.00} will give the
formatted amount of tax, to the nearest penny. To create the formula field,
simply press Ctrl-F9 to create the field braces (i.e. ' { }') and type your
equation between them. Numeric picture switch formatting also gives you the
ability to format +ve, -ve and 0 values independently of each other, if you
wish.

For more information how to format the fields and do a wide range of other
calculations in Word, check out my Word Field Maths 'tutorial', at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=365442

Cheers
 
J

JayM

Thanks Macropod

we have tried this before and unfortunately it doesn't always round off
correctly to the nearest pence. We then had to tell users how to unprotect
the form so that they could manually change it and this then caused other
problems. We also had a macro to print numerous copies with things like
"file copy" and "vat copy" added and the macro had to unprotect the document
again to do this. The VBA route seemed the far simpler but unfortunately a
colleague of mine wrote the original code which seemed far more complicated
then the code I wrote but my code has the problem with the formatting. I
could take the formatting off but it just looks nicer and more readable with
the commas.

Many thanks for your help.

macropod said:
Hi Jay,

You're making this harder than it needs to be.

Instead of using vba, you could simply set each formfield's properties to
'calculate on exit' and insert formula fields wherever you need the to
calculate and format the results.

For example, say you have two formfields that set the bookmarks 'Fees' and
'TaxRate' and you want to multiply these to get the amount of tax. In that
case, a formula field coded as {=Fees*TaxRate \# £,0.00} will give the
formatted amount of tax, to the nearest penny. To create the formula field,
simply press Ctrl-F9 to create the field braces (i.e. ' { }') and type your
equation between them. Numeric picture switch formatting also gives you the
ability to format +ve, -ve and 0 values independently of each other, if you
wish.

For more information how to format the fields and do a wide range of other
calculations in Word, check out my Word Field Maths 'tutorial', at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=365442

Cheers

--
macropod
[MVP - Microsoft Word]


JayM said:
I have a word document (invoice). I have written some code (it is probably
very crude code but it works - well sort of).

the user completes the sections of the table with the figures they require
costs, disbursements, disbursements inc vat etc. They then click on a macro
button which runs a macro that will calculate the vat that is required and
add it all together less any costs already paid and also formats the figures
to #,###,##0.00.

My problem occurs when the user then changes and figure and recalculates. A
figure of 13,776.00 becomes 13.00 it ignores the figures aftet the first
comma. I have pasted my code and would be grateful if anyone could see where
I have gone wrong or point me in a better direction.

Many thanks

JayM

Code
Sub TotalTableCellValues()
Dim cTable2B2 As Cell
Dim cTable2B3 As Cell
Dim cTable2B4 As Cell
Dim cTable2B5 As Cell
Dim cTable2B6 As Cell
Dim cTable2B7 As Cell
Dim cTable2B8 As Cell
Dim cTable2B9 As Cell
Dim cTable2B10 As Cell
Dim cTable2B11 As Cell
Dim cTable2B12 As Cell
Dim cSubTotalB6 As Cell
Dim cTotalB12 As Cell
Dim cSubTotalonVatB7 As Cell
Dim Fees As Currency
Dim Tfees As Currency
Dim Taxpaid As Currency
Dim Taxunpaid As Currency
Dim iSubTotal As Currency
Dim iVatonSubTotal As Currency
Dim Paid As Currency
Dim Unpaid As Currency
Dim iSubTotalincVat As Currency
Dim LessPaid As Currency
Dim Total As Currency


Set table2B2 = ActiveDocument.Tables(2).Cell(2, 2)
Set table2B3 = ActiveDocument.Tables(2).Cell(3, 2)
Set table2B4 = ActiveDocument.Tables(2).Cell(4, 2)
Set table2B5 = ActiveDocument.Tables(2).Cell(5, 2)
Set cSubTotalB6 = ActiveDocument.Tables(2).Cell(6, 2)
Set cVatonSubTotalB7 = ActiveDocument.Tables(2).Cell(7, 2)
Set table2B8 = ActiveDocument.Tables(2).Cell(8, 2)
Set table2B9 = ActiveDocument.Tables(2).Cell(9, 2)
Set table2B10 = ActiveDocument.Tables(2).Cell(10, 2)
Set table2B11 = ActiveDocument.Tables(2).Cell(11, 2)
Set cTotalB12 = ActiveDocument.Tables(2).Cell(12, 2)

iSubTotal = Val(table2B2.Range.Text) + Val(table2B3.Range.Text) +
Val(table2B4.Range.Text) + Val(table2B5.Range.Text)

cSubTotalB6.Range.Text = iSubTotal

cVatonSubTotalB7.Range.Text = iVatonSubTotal

iVatonSubTotal = Val(iSubTotal) * 17.5 / 100

iSubTotalincVat = Val(iSubTotal) + Val(iVatonSubTotal) +
Val(table2B8.Range.Text) + Val(table2B9.Range.Text)

Total = Val(iSubTotalincVat) - Val(table2B11.Range.Text)

cTotalB12.Range.Text = Total
Fees = Val(table2B2.Range.Text)
Tfees = Val(table2B3.Range.Text)
Taxpaid = Val(table2B4.Range.Text)
Taxunpaid = Val(table2B5.Range.Text)
Paid = Val(table2B8.Range.Text)
Unpaid = Val(table2B9.Range.Text)
LessPaid = Val(table2B11.Range.Text)

With ActiveDocument.Tables(2)
.Cell(2, 2).Range.Text = Format(Fees, "#,###,##0.00")
.Cell(3, 2).Range.Text = Format(Tfees, "#,###,##0.00")
.Cell(4, 2).Range.Text = Format(Taxpaid, "#,###,##0.00")
.Cell(5, 2).Range.Text = Format(Taxunpaid, "#,###,##0.00")
.Cell(6, 2).Range.Text = Format(iSubTotal, "#,###,##0.00")
.Cell(7, 2).Range.Text = Format(iVatonSubTotal, "#,###,##0.00")
.Cell(8, 2).Range.Text = Format(Paid, "#,###,##0.00")
.Cell(9, 2).Range.Text = Format(Unpaid, "#,###,##0.00")
.Cell(10, 2).Range.Text = Format(iSubTotalincVat, "#,###,##0.00")
.Cell(11, 2).Range.Text = Format(LessPaid, "#,###,##0.00")
.Cell(12, 2).Range.Text = Format(Total, "£" & "#,###,##0.00")

End With
End Sub
Click to expand...
Click to expand...
 
J

JayM

Jezebel

Many thanks for this. I will look at the VBA option you have given me but
being a novice I think it will take a little time.

The Excel option is a maybe because the users have to add some text in and
it seems a little slow opening up the spreadsheet to put the figures in - may
be I am missing a trick somewhere.

Thanks for your help
 
M

macropod

Hi Jay,

The document at the link I gave shows how to do whatever rounding you might
need.

Cheers

--
macropod
[MVP - Microsoft Word]


JayM said:
Thanks Macropod

we have tried this before and unfortunately it doesn't always round off
correctly to the nearest pence. We then had to tell users how to unprotect
the form so that they could manually change it and this then caused other
problems. We also had a macro to print numerous copies with things like
"file copy" and "vat copy" added and the macro had to unprotect the document
again to do this. The VBA route seemed the far simpler but unfortunately a
colleague of mine wrote the original code which seemed far more complicated
then the code I wrote but my code has the problem with the formatting. I
could take the formatting off but it just looks nicer and more readable with
the commas.

Many thanks for your help.

macropod said:
Hi Jay,

You're making this harder than it needs to be.

Instead of using vba, you could simply set each formfield's properties to
'calculate on exit' and insert formula fields wherever you need the to
calculate and format the results.

For example, say you have two formfields that set the bookmarks 'Fees' and
'TaxRate' and you want to multiply these to get the amount of tax. In that
case, a formula field coded as {=Fees*TaxRate \# £,0.00} will give the
formatted amount of tax, to the nearest penny. To create the formula field,
simply press Ctrl-F9 to create the field braces (i.e. ' { }') and type your
equation between them. Numeric picture switch formatting also gives you the
ability to format +ve, -ve and 0 values independently of each other, if you
wish.

For more information how to format the fields and do a wide range of other
calculations in Word, check out my Word Field Maths 'tutorial', at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=365442

Cheers

--
macropod
[MVP - Microsoft Word]


JayM said:
I have a word document (invoice). I have written some code (it is probably
very crude code but it works - well sort of).

the user completes the sections of the table with the figures they require
costs, disbursements, disbursements inc vat etc. They then click on a macro
button which runs a macro that will calculate the vat that is required and
add it all together less any costs already paid and also formats the figures
to #,###,##0.00.

My problem occurs when the user then changes and figure and
Click to expand...
recalculates.
A
figure of 13,776.00 becomes 13.00 it ignores the figures aftet the first
comma. I have pasted my code and would be grateful if anyone could
Click to expand...
see
where
I have gone wrong or point me in a better direction.

Many thanks

JayM

Code
Sub TotalTableCellValues()
Dim cTable2B2 As Cell
Dim cTable2B3 As Cell
Dim cTable2B4 As Cell
Dim cTable2B5 As Cell
Dim cTable2B6 As Cell
Dim cTable2B7 As Cell
Dim cTable2B8 As Cell
Dim cTable2B9 As Cell
Dim cTable2B10 As Cell
Dim cTable2B11 As Cell
Dim cTable2B12 As Cell
Dim cSubTotalB6 As Cell
Dim cTotalB12 As Cell
Dim cSubTotalonVatB7 As Cell
Dim Fees As Currency
Dim Tfees As Currency
Dim Taxpaid As Currency
Dim Taxunpaid As Currency
Dim iSubTotal As Currency
Dim iVatonSubTotal As Currency
Dim Paid As Currency
Dim Unpaid As Currency
Dim iSubTotalincVat As Currency
Dim LessPaid As Currency
Dim Total As Currency


Set table2B2 = ActiveDocument.Tables(2).Cell(2, 2)
Set table2B3 = ActiveDocument.Tables(2).Cell(3, 2)
Set table2B4 = ActiveDocument.Tables(2).Cell(4, 2)
Set table2B5 = ActiveDocument.Tables(2).Cell(5, 2)
Set cSubTotalB6 = ActiveDocument.Tables(2).Cell(6, 2)
Set cVatonSubTotalB7 = ActiveDocument.Tables(2).Cell(7, 2)
Set table2B8 = ActiveDocument.Tables(2).Cell(8, 2)
Set table2B9 = ActiveDocument.Tables(2).Cell(9, 2)
Set table2B10 = ActiveDocument.Tables(2).Cell(10, 2)
Set table2B11 = ActiveDocument.Tables(2).Cell(11, 2)
Set cTotalB12 = ActiveDocument.Tables(2).Cell(12, 2)

iSubTotal = Val(table2B2.Range.Text) + Val(table2B3.Range.Text) +
Val(table2B4.Range.Text) + Val(table2B5.Range.Text)

cSubTotalB6.Range.Text = iSubTotal

cVatonSubTotalB7.Range.Text = iVatonSubTotal

iVatonSubTotal = Val(iSubTotal) * 17.5 / 100

iSubTotalincVat = Val(iSubTotal) + Val(iVatonSubTotal) +
Val(table2B8.Range.Text) + Val(table2B9.Range.Text)

Total = Val(iSubTotalincVat) - Val(table2B11.Range.Text)

cTotalB12.Range.Text = Total
Fees = Val(table2B2.Range.Text)
Tfees = Val(table2B3.Range.Text)
Taxpaid = Val(table2B4.Range.Text)
Taxunpaid = Val(table2B5.Range.Text)
Paid = Val(table2B8.Range.Text)
Unpaid = Val(table2B9.Range.Text)
LessPaid = Val(table2B11.Range.Text)

With ActiveDocument.Tables(2)
.Cell(2, 2).Range.Text = Format(Fees, "#,###,##0.00")
.Cell(3, 2).Range.Text = Format(Tfees, "#,###,##0.00")
.Cell(4, 2).Range.Text = Format(Taxpaid, "#,###,##0.00")
.Cell(5, 2).Range.Text = Format(Taxunpaid, "#,###,##0.00")
.Cell(6, 2).Range.Text = Format(iSubTotal, "#,###,##0.00")
.Cell(7, 2).Range.Text = Format(iVatonSubTotal, "#,###,##0.00")
.Cell(8, 2).Range.Text = Format(Paid, "#,###,##0.00")
.Cell(9, 2).Range.Text = Format(Unpaid, "#,###,##0.00")
.Cell(10, 2).Range.Text = Format(iSubTotalincVat, "#,###,##0.00")
.Cell(11, 2).Range.Text = Format(LessPaid, "#,###,##0.00")
.Cell(12, 2).Range.Text = Format(Total, "£" & "#,###,##0.00")

End With
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

formatting currency 2
Problem with Worksheet and Table cells 1
Add a new row with formfields in cells 2-4 2
Delete colums in a table using a macro if 2 column is empty 0
Read text from table in a document, place it in a userform label,then delete the table 2
paragraph format on selected cells in a table 2
Find string in table and move to next cell 1
Read text from table cells and write to a plain text file 9

Top