Calculating a table in word and applying formatting

[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



:think:

[fumei]

1. Could you please use the underscore character to make your code multi-line. Otherwise it causes R E A L L Y wide code windows here. It is a pain to have to scroll left/right as well as up/down. Thanks. Like this:iSubTotalincVat = Val(iSubTotal) + Val(iVatonSubTotal) + _
Val(table2B8.Range.Text) + Val(table2B9.Range.Text)


2. Uh...is it absolutely required you do this in Word? Why are you not doing this in Excel?

[lucas]

Hi Jay,
I have edited your code to comply with Gerry's small screen res. It does help as I have a large screen and it was running off of my screen also.

I also agree with Gerry that this is exactly the kind of project that Excel was designed for....Much easier to accomplish your goals and then if necessary it can be put in Word for printing but shouldn't be necessary.

[fumei]

Steve? You have edited his post? Still wide, wide, wide, for me.

[lucas]

Yes I did edit it once...tried again just now and its probably still too wide for your screen Gerry. Let me know.

[JayM]

2. Uh...is it absolutely required you do this in Word? Why are you not doing this in Excel?

Many thanks for altering the thread I hadn't realised it was such a problem - sorry!

I could probably do this in excel but we have so many users that have no experience of excel and not all pc's a running it either.
I tried just inserting the excel bit into word but this was slow and a bit cumbersome. The users also need to add text in to show what disbursements are required it just looked better in word.

Can you use word vba to open an excel document in excel?