Insert field in formula with VBA

gmayor · #4 02-14-2022, 05:06 AM

Frankly I wouldn't do it like that. As you are going to have to change the fields in the documents, I would suggest that you use instead content controls.

If there are nine different years then use nine content controls each with a different title e.g. - Date1 to Date9, where Date1 is the first date i.e. 2020 and Date9 is the last date i.e. 2028. You can use the same field titles wherever you wish to repeat one or more of the dates.

Select the existing date in the document and run Macro1 to create and title the content control in place of the date, This only has to be done once.

Depending on how the dates are currently inserted, it may be possible to automate this to update them all at once, but as shown you put the cursor in a date and run the Macro1

Then it is a simple matter to update all the Date content controls in the document using Macro2.

You can change the defaults in the input boxes as required, but the start value is always the same for each content control in Macro1.

Code:

Sub Macro1()
Const strList As String = "0123456789"
Dim sStart As String, sText As String
Dim iDate As Integer
Dim oRng As Range
Dim oCC As ContentControl

    sStart = InputBox("Enter lowest/first year in the document", "Start Year", "2016")

    Set oRng = Selection.Range
    oRng.MoveStartWhile strList, wdBackward
    oRng.MoveEndWhile strList
    sText = oRng.Text
    iDate = CInt(oRng.Text) - CInt(sStart)
    Set oCC = oRng.ContentControls.Add(wdContentControlRichText)
    With oCC
        .Title = "Date" & CStr(iDate + 1)
        .Tag = .Title
        .Range.Text = sText
        .LockContentControl = True
    End With
    Set oCC = Nothing
    Set oRng = Nothing
End Sub

Sub Macro2()
Dim oCC As ContentControl
Dim sStart As String
    sStart = InputBox("Enter lowest/first year in the document", "Start Year", "2020")
    For Each oCC In ActiveDocument.ContentControls
        If oCC.Title Like "Date?" Then
            oCC.Range.Text = CStr(Val(sStart) + Val(Right(oCC.Title, 1) - 1))
        End If
    Next oCC
    Set oCC = Nothing
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Possible to use an existing vlookup formula to also insert correct info and trigger a SUM formula	innkeeper9	Excel	2	09-13-2016 08:59 PM
Insert a field	CepheiMD	Word	2	03-18-2016 01:56 AM
insert database as field	david_89_	Mail Merge	3	03-26-2014 06:02 AM
Formula Field using Field Codes	hunter2193	Word	3	04-05-2013 04:58 AM
Formula to subtract one month from due date field in reminder field	ghumdinger	Outlook	1	10-01-2011 12:09 AM