update cell references when calculation field is pasted to a new r

[ivory_kitten]

I have a form field with calculation B2*C2 with the result in D2, how can I
paste the formula to the rest of the rows and have the cell reference update
to that row number. In excel you just fill the formula down and it
automatically updates?

Haven't been able to find any clues in the newsgroups!

 

[Ithaca]

to get that to calculation to multiple cells/row down the page just highligh
B, C, and D and drage them down however far you want.

Does this help?

 

[ivory_kitten]

Yes, that would work in excel

It does not work in Word though

 

[Greg Maxey]

Well you can just paste. You could build a macro that would do it for
you (may be more trouble than it is worth). Here is an example:

Just select the cells in column D under your existing formula

Sub FillWithFormula()
Dim i As Long
Dim j As Long
Dim oDoc As Document
Dim myRng As Word.Range
Dim myFormField As FormField
Dim pFormulaStr As String
Set oDoc = ActiveDocument
'Find out the starting row number of the selection
'This should be 3 in your case
j = Selection.Information(wdStartOfRangeRowNumber)
For i = j To Selection.Cells.Count + j
pFormulaStr = "=B" & i & "*C" & i
Set myRng = oDoc.Tables(1).Cell(i, 4).Range
myRng.Collapse wdCollapseStart
Set myFormField = oDoc.FormFields.Add(Range:=myRng, _
Type:=wdFieldFormTextInput)
myFormField.TextInput.EditType Type:=wdCalculationText, _
Default:=pFormulaStr, _
Format:="0.00", Enabled:=False
Next i
End Sub

 

[Jean-Guy Marcil]

ivory_kitten was telling us:
ivory_kitten nous racontait que :

I have a form field with calculation B2*C2 with the result in D2, how
can I paste the formula to the rest of the rows and have the cell
reference update to that row number. In excel you just fill the
formula down and it automatically updates?

Haven't been able to find any clues in the newsgroups!

You can use dynamic formulas.
Here is little something I picked up in the groups, probbably from macrpod:

<quote>
Say you need a formula on every row to multiply the contents of ColumnA by
the contents of ColumnB, then add the contents of ColumnC, and your formula
starts on Row1 of the table. To do that you could use a compound field like:

{QUOTE {Set CellA "a{={SEQ RowNr}/2}"}
{Set CellB "b{={SEQ RowNr \c}/2}"}
{Set CellC "c{={SEQ RowNr \c}/2}"}
{={CellA}*{CellB}+{CellC} \# 0;-0}
}
where the braces '{}' are entered in pairs via Ctrl-F9. I've laid the field
out this way for readability - you can dispense with the internal CRs.

This field works by creating a sequence number of each row and incorporating
that plus the required column letters into bookmarks (CellA, CellB, CellC)
for those rows. These then become the cell addresses referenced in the
formula. You'll notice that the SEQ field has a the \c switch for the CellB,
CellC references, but not for the CellA reference. This is to stop multiple
SEQ references on the same row changing the SEQ No. (and hence the source
row number). You'll also notice that each bookmark includes a '/2' to divide
the SEQ No by 2. That's needed because of a flaw in the way Word updates SEQ
fields when used directly in a cell reference.

If your data doesn't start on the first row in the table, you need to add an
offset to the formula for each row before the first data row. So, if your
data starts on the second row, you'd put +1 after each '/2' expression (i.e.
{SEQ RowNr}/2+1, etc). If the data starts on the third row, you use +2, and
so on. Use the same technique to offset the cell referencing by a
predetermined number of rows, using -ve values to refer to rows above, and
+ve values to refer to rows below.

Relative referencing does not work for columns.
<unquote>

So, in each cell where you want the result, you would need:

{QUOTE
{Set CellB "b{={SEQ RowNr}/2}"}
{Set CellC "c{={SEQ RowNr \c}/2}"}
{={CellB}*{CellC} \# 0;-0;}
}



--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[ivory_kitten]

I just tried your suggestion and I get !Syntax error!

I did it exactly like you had, with the +1 for the first row has column
heading in it and i used Ctrl+F9 to insert the brackets!?

Any suggestions?

 

[ivory_kitten]

When I copy and past the formula still references B2*C2 and does not update
to B3*C3 etc.

I tried your macro but it just gives me a debug message at Set myRng part.
Does it matter that I have another table above this one with the calculation?

 

[Greg Maxey]

Yes is matters.
Set myRng = oDoc.Tables(1).Cell(i, 4).Range

If you have one table before the one that you are working with then use"

.... Tables(2)...

or you might use

Selection.Tables(1).Cell(i.4).Range

 

[Doug Robbins - Word MVP]

Greg probably meant to say "Well, you can't just paste"

His macro should work, except that the line

For i = j To Selection.Cells.Count + j

should be

For i = j To Selection.Cells.Count + j - 1

or, the command

pFormulaStr = "=B" & i & "*C" & i

must be moved to after the command

Set myRng = oDoc.Tables(1).Cell(i, 4).Range

Otherwise, the macro will insert two formulae in the last cell in the range.

The following macro inserts regular Formula fields rather than a FormField
containing a formula, but both work equally well

Dim i As Long
Dim j As Long
Dim oDoc As Document
Dim myRng As Word.Range
Dim pFormulaStr As String
Set oDoc = ActiveDocument
'Find out the starting row number of the selection
'This should be 3 in your case
j = Selection.Information(wdStartOfRangeRowNumber)
For i = j To Selection.Cells.Count + j - 1
pFormulaStr = "=B" & i & "*C" & i
Set myRng = oDoc.Tables(1).Cell(i, 4).Range
myRng.Collapse wdCollapseStart
oDoc.Fields.Add Range:=myRng, Text:=pFormulaStr
Next i


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Greg Maxey]

Yikes, yes I did mean to say "Well you can't just paste."

I can't confirm the second part of your contention as I happen to be in
deep discussion with Mr. James (aka Jim) Beam at present and he isn't
thinking so clearly ;-)

IK, I did'nt mean to confuse, and I hope Doug had cleared things up for
you.

 

[Jean-Guy Marcil]

ivory_kitten was telling us:
ivory_kitten nous racontait que :

I just tried your suggestion and I get !Syntax error!

I did it exactly like you had, with the +1 for the first row has
column heading in it and i used Ctrl+F9 to insert the brackets!?

Any suggestions?

Post the formula you have been using, exactly as you tried it in your
document.
It works for me... if it does not for you it means you have an error
somewhere in the formula. It is very easy to make a mistake with longer
formulas.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org