Microsoft Office Tutorials and References
In Depth Information
Tables: Copying Formulas
'Don't include the original cell¶
'in the target¶
Do While sel.Rows(1).Index = startRow¶
MsgBox "The selection is still in the same row. " & _¶
"The formula cannot be copied. " & _¶
"Please try again.", vbCritical + vbOKOnly¶
'Reshow the form¶
StartCopyFormula¶
Exit Sub¶
Loop¶
'Go through each cell in the contiguous¶
'selection¶
For Each cel In Selection.Cells¶
newFormula = frm.txtFieldCode¶
'Process each formula part the user¶
'selected to be changed relative to the row¶
For i = 0 To frm.lstFormulas.ListCount - 1¶
partFormula = frm.lstFormulas.List(i)¶
'get start position of the formula part¶
posStart = InStr(newFormula, partFormula)¶
'if it was found¶
If posStart > 0 Then¶
'determine the end position¶
posEnd = posStart + Len(partFormula) - 1¶
'replace the row index in the formula¶
updatedFormula = UpdateFormula(partFormula, _¶
cel.Row.Index)¶
'replace the partial formula¶
newFormula = Replace(newFormula, _¶
partFormula, updatedFormula)¶
End If¶
Next¶
'insert the field code in the cell¶
'Variable declaration¶
Dim rngCel As Word.Range¶
Set rngCel = cel.Range¶
rngCel.Collapse wdCollapseStart¶
ActiveDocument.Fields.Add Range:=rngCel,
Type:=wdFieldEmpty, _¶
Text:=newFormula, PreserveFormatting:=False¶
Next¶
Else¶
MsgBox "The selection must be within a table", _¶
vbCritical vbOKOnly¶
StartCopyFormula¶
Exit Sub¶
End If¶
Unload frm¶
End Sub¶
Wrd
Search JabSto ::




Custom Search