Calculate a table cell

[Steve Wylie]

I have a request for what I assume will be a short macro, and would be very
grateful if an experienced reader of this newsgroup could reply with the
necessary sub-routine to accomplish it.

I've got three separate Word documents each containing lots of tables with
figures in them. What I need for each of the figures in each of these
tables is to calculate its percentage of a given figure. The given figure
will stay constant, and so can be coded into the macro.

So what I need is a macro where I place the cursor in a Word table cell
containing a figure (always an integer), and it will take the figure (eg
36), calculate it as a percentage of a constant (eg 427), move down one cell
and place the result, rounded to 2 decimal places, into this empty cell,
followed by a percent sign (ie 8.43%).

That's basically it. I did try having a bash at this myself, but although I
could read the contents of a cell as a string, my knowledge of Word VBA is
not sufficient to enable me to convert it to a number to perform the
calculation. Much less come up with the necessary calculation.

I would prefer not to paste the tables into Excel, do the calculations and
paste them back, due to the amount of these tables I have to deal with. It
just seems easier leaving them in Word so I don't louse up the formatting by
copying and pasting between Word and Excel. I thought of using a Word table
formula for this but the field code would need to be relative, not absolute,
so I could just paste it into each required table cell unchanged, and I
don't think Word tables do relative formulae...

And no, it's not a High School assignment! I work for a living and I'm 37!

If anyone could help me out here, it would be much appreciated.

Steve Wylie
Kent
England

 

[Doug Robbins]

Use when the selection is in the cell containing the number.

Dim i As Long, j As Long, numrange As Range
i = Selection.Information(wdEndOfRangeColumnNumber)
j = Selection.Information(wdEndOfRangeRowNumber)
Set numrange = Selection.Cells(1).Range
numrange.End = numrange.End - 1
Selection.Tables(1).Cell(j + 1, i).Range = Format(numrange / 427, "#.00%")


--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

 

[Jezebel]

You don't need macros to do this.

1. Add a bookmark to define the cell containing the number that will be
added (36 in your example), called eg "Val1"

2. In the cell below, add a field: { = 100 * Val1 / 427 \# "0.00%" }

 

[Steve Wylie]

Thank you, Doug - this is just what I was looking for.

Steve

 

[Steve Wylie]

1. Add a bookmark to define the cell containing the number that will be

added (36 in your example), called eg "Val1"

2. In the cell below, add a field: { = 100 * Val1 / 427 \# "0.00%" }

Ah yes, but I would have to come up with hundreds of bookmarks to
cover each and every cell that needed a calculation, plus pasting a
field into every cell that the percentage would appear in. Whereas
with a macro I can just play-and-go!

Thanks for the response anyway.

Steve

 

[Jezebel]

If you have hundreds of them, why on earth are you using Word rather than
Excel?

 

[Steve Wylie]

I didn't set up the original Word document. I think it started off as
somebody's small document and raged out of control!

Steve