Dynamic Table Column Calculation

[Nick C]

Hi

I am currently playing with VBA / Macros

I have created a table with 4 columns

Column 1 : Name
Column 2 : Wages Amount (Number)
Column 3 : Deduction Rate (number / percentage)
Column 4 : Calculated field of Wages - Deduction

I have added a macro to add a row and delete a row depending on when people
leave.

The rest of the time the details are saved in the document

I wish to have assistance with have a script which basically will go down on
column 4 and work out the calculation and put the value in.

I don't know weather it is better to calculate it just via coding (which I
need help with) or to put a formula in the cell from code down each valid
row in the table and then get the whole table to calculate.

Can some one assist me with the basic concept.

many thanks

Devo

 

[Malcolm Smith]

I have made countless Invoice templates and I have always used it by
calculating the contents of the cells rather than put calculation fields
in the table.

- Malc

 

[Nick C]

So do you have a code snippet explain how this is done whilst going down the
row please

 

[Malcolm Smith]

All you have to do is to work out the number of .Rows in the table.

If the topmost row contains labels then you won't want to start there.
Then all you do is to take the Cell(y, x ).Range.Text of each cell in the
relevant column of the table.

I would strip off the last characters of the text (the end of cell
marker), remove any thousands separators (whether they be commas or
decimal points) and the currency name (if any).

Add the whole lot up. And format the result and shove it where you want
it.

If you want it in the last row of the table then remember to make the
..Text of the .Cell(y, x).Range set to an empty string so that it doesn't
get added in.

All it is a couple of loops, a bit of cleaning up and addition.

This way it's possible to have variable number of rows to the table.
Though, why not have it in a database; wouldn't that be easier?

- Malc
www.dragondrop.com

 

[Nick C]

Thanks for the replies..

So something like this would work

Dim howmanyrows As Integer
Dim currentrow As Integer
Dim nettamount As Long
Dim deductionamount As Long
Dim grosstotal As Long

howmanyrows = Selection.Information(wdEndOfRangeRowNumber)
For currentrow = 2 To howmanyrows
----> dont know what commands to read and write from a cell can you assit
read nettamount out of current row cell 2
read deductionamount out of current row cell 3
grosstotal = nettamount*((deductionamount/100)+1)
place grosstotal in currentrow cell 4
currentrow = currentrow +1
Next

End Sub

I am sorry but i am a real newbie.. and am just playing at the moment. This
is a form that gets done once a week but this little operation will save 20
mins for the guy that asked me to do it.

So i thought I would try.

If you can assist .. i would be gratefull

Devo

 

[Malcolm Smith]

Devo

Send me the template or document to the address which is on my site and I
will see what I can do.

First two questions:

1. What is your symbol for the thousands divider (in the UK it's a comma
and in Norway it's a period)? I could spend time hunting through the
registry but since I am not charging for this...

2. What is the currency name or symbol that you have? Does it appear in
the data anywhere and if so does it appear at the start of the end of each
amount?

- Malc
www.dragondrop.com

 

[macropod]

Hi Devo,

You could use vba code to loop through the rows, putting
into each a field with the string:
=b#*(1-c#)
where # is the row number. Alternatively, you could do the
calculation entirely in vba and simply paste the results
into each cell. The advantage of inserting a field with
the calc is that the result can be updated via F9 if the
values in the other cells change.

You could also create a dynamic field in vba that you
could simply copy & paste to each cell, without needing to
update the Row Nos., but creating this is much more
complicated. It involves several levels of nested fields
and would end up looking like:
{QUOTE{Set CellB "b{={SEQ RowNr}/2}"}{Set CellC "c{={SEQ
RowNr \c}/2}"}{={CellB}*(1-{CellC}) \# $,#.00}}

Cheers