How to find a specific word and replace the immediate next word

[sugaprasad]

Hi All,

I'm searching for a particular word in a word document using a macro. Say for example i'm searching the word "Amount". I need to replace the very next word of the found word. Suppose there is a sentence like "Amount $5,000". I will search for the word "Amount" and have to replace the $5,000 with some other value.

One can raise a doubt why need to search for "Amount" instead of "$5,000". I will tell the reason. $5,000 will not be same in all the documents. whereas "Amount" will be constant. So I'm invoking a search t find the word "Amount" and replace the very next word. I did some coding. Can you please let me know where should i have to correct.

Set rng = objWord.ActiveDocument.Content
        rng.Find.Execute findText:="Amount", MatchCase:=True, Forward:=True
            If rng.Find.Found Then
                If rng.Information(wdWithInTable) Then
                    rng.Next.Sentences(1) = "$7,500"               
                End If
            Else
                    MsgBox "Not found"                    
            End If

[sugaprasad]

Hi.. anyone got idea on this??

[OllieB]

You can use a single find & replace instruction for the entire document using wildcards

pvt_obj_Document.StoryRanges(wdMainTextStory).Find.Execute _
                    FindText:="Amount $????????", MatchCase:=True, MatchWholeWord:=False, ReplaceWith:="Amount $ 7,500", MatchWildcards:=True, Replace:=wdReplaceAll

[sugaprasad]

You can use a single find & replace instruction for the entire document using wildcards

pvt_obj_Document.StoryRanges(wdMainTextStory).Find.Execute _
                    FindText:="Amount $????????", MatchCase:=True, MatchWholeWord:=False, ReplaceWith:="Amount $ 7,500", MatchWildcards:=True, Replace:=wdReplaceAll

Thank You so much for your reply. Your idea works fine but for onething. Here in this place the word will be "Amount $?????". it means the word Amount will be BOLD always. so when i replace that word with "Amount $ 7,500" $7,500 also getting bold. I dont want it to be bold. Any suggestion??

[OllieB]

Yes:
- set the format on the first find & replace to normal font (to remove the BOLD);
- run a second find & replace for the "Amount" string only and set format to BOLD

[sugaprasad]

Yes:
- set the format on the first find & replace to normal font (to remove the BOLD);
- run a second find & replace for the "Amount" string only and set format to BOLD

I did this after replacing the word. But unfortunately its not working.

rng.Find.Execute FindText:="$7,500", Forward:=True
         Selection.Font.Bold = False

am i missing something?

[OllieB]

I just recorded a MACRO with the actions and it worked without a problem.

Please see the attached code

Sub Macro1()
     Selection.Find.ClearFormatting
     Selection.Find.Replacement.ClearFormatting
     With Selection.Find.Replacement.Font
          .Bold = False
          .Italic = False
     End With
     With Selection.Find
          .Text = "Amount $????????"
          .Replacement.Text = "Amount # 7,500"
          .Forward = True
          .Wrap = wdFindContinue
          .Format = True
          .MatchCase = False
          .MatchWholeWord = False
          .MatchAllWordForms = False
          .MatchSoundsLike = False
          .MatchWildcards = True
     End With
     Selection.Find.Execute Replace:=wdReplaceAll
     Selection.Find.ClearFormatting
     Selection.Find.Replacement.ClearFormatting
     Selection.Find.Replacement.Font.Bold = True
     With Selection.Find
          .Text = "Amount"
          .Replacement.Text = "Amount"
          .Forward = True
          .Wrap = wdFindContinue
          .Format = True
          .MatchCase = False
          .MatchWholeWord = False
          .MatchWildcards = False
          .MatchSoundsLike = False
          .MatchAllWordForms = False
     End With
     Selection.Find.Execute Replace:=wdReplaceAll
     ShowVisualBasicEditor = True
End Sub

[sugaprasad]

I just recorded a MACRO with the actions and it worked without a problem.

Please see the attached code

Sub Macro1()
     Selection.Find.ClearFormatting
     Selection.Find.Replacement.ClearFormatting
     With Selection.Find.Replacement.Font
          .Bold = False
          .Italic = False
     End With
     With Selection.Find
          .Text = "Amount $????????"
          .Replacement.Text = "Amount # 7,500"
          .Forward = True
          .Wrap = wdFindContinue
          .Format = True
          .MatchCase = False
          .MatchWholeWord = False
          .MatchAllWordForms = False
          .MatchSoundsLike = False
          .MatchWildcards = True
     End With
     Selection.Find.Execute Replace:=wdReplaceAll
     Selection.Find.ClearFormatting
     Selection.Find.Replacement.ClearFormatting
     Selection.Find.Replacement.Font.Bold = True
     With Selection.Find
          .Text = "Amount"
          .Replacement.Text = "Amount"
          .Forward = True
          .Wrap = wdFindContinue
          .Format = True
          .MatchCase = False
          .MatchWholeWord = False
          .MatchWildcards = False
          .MatchSoundsLike = False
          .MatchAllWordForms = False
     End With
     Selection.Find.Execute Replace:=wdReplaceAll
     ShowVisualBasicEditor = True
End Sub

when i use this part of code i'm getting wrong number of arguments or invalid property assignment error. can u please let me know what should i do. Ive used Option Explicit in the top

     With Selection.Find
          .Text = "Amount $????????"
          .Replacement.Text = "Amount # 7,500"
          .Forward = True
          .Wrap = wdFindContinue
          .Format = True
          .MatchCase = False
          .MatchWholeWord = False
          .MatchAllWordForms = False
          .MatchSoundsLike = False
          .MatchWildcards = True
     End With

[OllieB]

The "Selection" part does not work because you are working from Excel and not from within Word. I recorded the macro in Word so you will need the selection variable to rng like you have done yourself in the first post.

     Set rng = objWord.ActiveDocument.Content
     With rng.Find
          .ClearFormatting
          .Replacement.ClearFormatting
          .Replacement.Font.Bold = False
          .Replacement.Font.Italic = False
          .Text = "Amount $????????"
          .Replacement.Text = "Amount # 7,500"
          .Forward = True
          .Wrap = wdFindContinue
          .Format = True
          .MatchCase = False
          .MatchWholeWord = False
          .MatchAllWordForms = False
          .MatchSoundsLike = False
          .MatchWildcards = True
          .Execute Replace:=wdReplaceAll
     End With
     With rng.Find
          .ClearFormatting
          .Replacement.ClearFormatting
          .Replacement.Font.Bold = True
          .Text = "Amount"
          .Replacement.Text = "Amount"
          .Forward = True
          .Wrap = wdFindContinue
          .Format = True
          .MatchCase = False
          .MatchWholeWord = False
          .MatchWildcards = False
          .MatchSoundsLike = False
          .MatchAllWordForms = False
          .Execute Replace:=wdReplaceAll
     End With

[sugaprasad]

The "Selection" part does not work because you are working from Excel and not from within Word. I recorded the macro in Word so you will need the selection variable to rng like you have done yourself in the first post.

     Set rng = objWord.ActiveDocument.Content
     With rng.Find
          .ClearFormatting
          .Replacement.ClearFormatting
          .Replacement.Font.Bold = False
          .Replacement.Font.Italic = False
          .Text = "Amount $????????"
          .Replacement.Text = "Amount # 7,500"
          .Forward = True
          .Wrap = wdFindContinue
          .Format = True
          .MatchCase = False
          .MatchWholeWord = False
          .MatchAllWordForms = False
          .MatchSoundsLike = False
          .MatchWildcards = True
          .Execute Replace:=wdReplaceAll
     End With
     With rng.Find
          .ClearFormatting
          .Replacement.ClearFormatting
          .Replacement.Font.Bold = True
          .Text = "Amount"
          .Replacement.Text = "Amount"
          .Forward = True
          .Wrap = wdFindContinue
          .Format = True
          .MatchCase = False
          .MatchWholeWord = False
          .MatchWildcards = False
          .MatchSoundsLike = False
          .MatchAllWordForms = False
          .Execute Replace:=wdReplaceAll
     End With

Thanks that's working great.
I just faced one problem.

"Amount $????????"
Here the dollar value will not be always same. It will change everytime. So it can't be fixed. So is there anyother way where i can extract the next word of the Amount.

I'm sorry to bug you all the time when i need a help.

[OllieB]

I am not sure what you mean. The Find "Amount $????????" will find every occurrence of the string Amount $.... irrespective of the actual amount shown after the $, just like you asked.

[sugaprasad]

I am not sure what you mean. The Find "Amount $????????" will find every occurrence of the string Amount $.... irrespective of the actual amount shown after the $, just like you asked.

yes you are right.. Let me tell you clearly. I have these two sentences with bullets.

• Amount $5,000
• Payment $8,500

so now "Amount $????????" replacing some part of next sentence as well.(ie its getting replaced as Amount $7,500yment $8,500). And also $5,000 can be $15,000 at times. so we can't fix the number of Question marks. This is my concern here. Hope i'm clear

[OllieB]

If this is your problem, the automated find & replace solution will only work if you know the length of the amount string you are replacing. If you do not this up front, then the current solution we are discussing will not work and you are back at square one.

[sugaprasad]

If this is your problem, the automated find & replace solution will only work if you know the length of the amount string you are replacing. If you do not this up front, then the current solution we are discussing will not work and you are back at square one.

OOPS.. my bad.. I know this is too much to ask you...you got any idea for my problem?

[OllieB]

A dirty way to do this, is to consider that the contents of the document can also be read/updated as a string

Activedocument.Content.Text

So if your documents are not too large you could use a regular search loop in VBA to find Amount and isolate and replace the $value. However if your documents are very large I do not know how VBA will handle the size of the string. Word is not my speciality.

[OllieB]

For "small" document the following might work (replace new_amount with actual value provided as a parameter or something like that)

'#
'# declare
'#
     Dim lngFound As Long
     Dim lngStart As Long
     Dim lngAmountStart As Long
     Dim lngAmountEnd As Long
     Dim rngContent As Range
     
'#
'# initialise
'#
     lngStart = 1
     Set rngContent = objWord.ActiveDocument.Content
'#
'# loop though the contents
'#
     lngFound = InStr(lngStart, rngContent.Text, "Amount")
     While lngFound > 0
          '#
          '# find the currency symbol
          '#
               lngStart = lngFound + 6
               lngFound = InStr(lngStart, rngContent.Text, "$")
          '#
          '# determine the starting position of the amount taking into consideration
          '# that the currency symbol may be followed by a space
          '#
               If Mid$(rngContent.Text, lngFound + 1, 1) = " " Then
                    lngAmountStart = lngFound + 2
               Else
                    lngAmountStart = lngFound + 1
               End If
          '#
          '# determine the ending position for the amount identified by a space
          '#
               lngAmountEnd = InStr(lngAmountStart, rngContent.Text, " ")
          '#
          '# replace the existing amount value with the desired replacement
          '#
               ActiveDocument.Content = Left$(rngContent.Text, lngAmountStart - 1) & "new_amount" & Mid$(rngContent.Text, lngAmountEnd)
          
          '#
          '# find the next occurrence of the text Amount
          '#
               lngStart = lngAmountEnd
               lngFound = InStr(lngStart, rngContent.Text, "Amount")
     Wend

[sugaprasad]

For "small" document the following might work (replace new_amount with actual value provided as a parameter or something like that)

'#
'# declare
'#
     Dim lngFound As Long
     Dim lngStart As Long
     Dim lngAmountStart As Long
     Dim lngAmountEnd As Long
     Dim rngContent As Range
     
'#
'# initialise
'#
     lngStart = 1
     Set rngContent = objWord.ActiveDocument.Content
'#
'# loop though the contents
'#
     lngFound = InStr(lngStart, rngContent.Text, "Amount")
     While lngFound > 0
          '#
          '# find the currency symbol
          '#
               lngStart = lngFound + 6
               lngFound = InStr(lngStart, rngContent.Text, "$")
          '#
          '# determine the starting position of the amount taking into consideration
          '# that the currency symbol may be followed by a space
          '#
               If Mid$(rngContent.Text, lngFound + 1, 1) = " " Then
                    lngAmountStart = lngFound + 2
               Else
                    lngAmountStart = lngFound + 1
               End If
          '#
          '# determine the ending position for the amount identified by a space
          '#
               lngAmountEnd = InStr(lngAmountStart, rngContent.Text, " ")
          '#
          '# replace the existing amount value with the desired replacement
          '#
               ActiveDocument.Content = Left$(rngContent.Text, lngAmountStart - 1) & "new_amount" & Mid$(rngContent.Text, lngAmountEnd)
          
          '#
          '# find the next occurrence of the text Amount
          '#
               lngStart = lngAmountEnd
               lngFound = InStr(lngStart, rngContent.Text, "Amount")
     Wend

Thanks much for your help. I will try this.