VBA to replace date at end of paragraph

[SmallFry]

I have a paragraph which spans just one sentence. It's composed of left aligned text at the beginning, followed by a tab, and then text right aligned.

I would like to replace the data at the end with 2020-1, today's date

Before
Monthly Distributions <tab> 2014-1, 2015-1, 2018-1, 26 June2018

After
Monthly Distributions <tab> 2014-1, 2015-1, 2018-1, 2020-1, 3 November 2019

[HansV]

Is it OK to place the insertion point in the paragraph before running the macro, or should the macro somehow find that paragraph?

[SmallFry]

Hi Hans,

Sorry about that, yes, the macro should find the paragraph and insert the new text.

[HansV]

What should we search for? "left aligned text at the beginning, followed by a tab, and then text right aligned" is rather vague as a search condition...

[SmallFry]

Ultimately, I'm trying to replace the date at the end of the paragraph with 2020-1, today's date.

The sentence will always begin with Monthly Distributions, so I was thinking that is the key words to search for to identify the paragraph. It's consistent, but everything after is not. Just trying to change the end of the paragraph.

Part's in bold below

Before
Monthly Distributions <tab> 2014-1, 2015-1, 2018-1, 26 June2018 - Replace this

After
Monthly Distributions <tab> 2014-1, 2015-1, 2018-1, 2020-1, 3 November 2019 - With this

[HansV]

Try this:

Code: Select all

Sub AdjustParagraph()
    Dim strText As String
    Dim lngPos As Long
    Selection.HomeKey Unit:=wdStory
    Selection.Find.Execute FindText:="Monthly Distributions"
    With Selection.Paragraphs(1).Range
        strText = .Text
        lngPos = InStrRev(strText, ",")
        strText = Left(strText, lngPos) & " 2020-1, " & Format(Date, "d mmmm yyyy") & vbCr
        .Text = strText
    End With
End Sub

[SmallFry]

Thank you Hans. This works great.