Bookmarks replacing using If and condition

nmc

New Member
Joined
Aug 25, 2022
Messages
38
Office Version
  1. 2021
Platform
  1. Windows
Hello.

I'm trying to replace considering the situation above:

If the content of column B (Navette.Sheet) = Yes And name of the cell in column B (Navette.Sheet) equal to the content of column D (Replace Workbook - Clause Sheet) then
Replace the bookmarks considering the content of column F (Replace Workbook - Clause Sheet) Else do Nothing

VBA Code:
[/
With wordDoc
Dim bm As Bookmark
If Navette.Range("A" & row).Value = "Yes" And Navette.Range("B" & row).Name) = Replace.Sheet("D" & row) Then
For Each bm In ActiveDocument.Bookmarks
ActiveDocument.Bookmarks (Replace.Range("F" & row).Value)
Else
Nothing
On Error Goto Next
Next
]
But I'm getting error, can someone help?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
VBA Code:
With wordDoc
    Dim bm          As Bookmark
    If Navette.Range("B" & row).Value = "Yes" And Navette.Range("B" & row).Value = Replace.Range("D" & row).Value Then
        For Each bm In ActiveDocument.Bookmarks
            If bm.Name = Replace.Range("F" & row).Value Then
                bm.Range.Text = Replace.Range("F" & row).Value
            End If
        Next bm
    End If
End With
 
Upvote 0
I made an example to test it.
Recommendation: Do not use reserved words for variables or for objects, for example Row, Replace. Use for example nRow, shReplace.

Rich (BB code):
Sub replacebookmark()
  Dim objWord As Word.Application
  Dim bm As Bookmark
  Dim nRow As Long
  Dim Navette As Worksheet
  Dim shReplace As Worksheet
  
  Set shReplace = ThisWorkbook.Sheets("clause")
  Set Navette = ThisWorkbook.Sheets("Navette")
 
  Set objWord = CreateObject("Word.Application")
  objWord.Visible = True
  objWord.Documents.Open "C:\trabajo\files\bookmark.docx"
  
  nRow = 2

  If Navette.Range("A" & nRow).Value = "Yes" And _
     Navette.Range("B" & nRow).Name.Name = shReplace.Range("D" & nRow).Value Then
    For Each bm In objWord.ActiveDocument.Bookmarks
      bm.Range.Text = shReplace.Range("F" & nRow).Value
    Next
  End If
End Sub
 
Upvote 0
It's not working.
I tried instead a new way to go further:
I have a word named Clauses.docx that has bookmarks with content controls
If cell value in column A of Navette Sheet equal to content control titles in Clause.docx then replace with column B values of Navette Sheet

After

If Column B of Navette.Sheet Name = Bookmark Name and Cell Value of that cell = "Yes"
Replace the bookmarks in wordDoc (doc file) with the bookmarks of Clauses.docx (the name of bookmarks on each document are the same)
Can you help? I'm struggling about almost a week on this thing
 
Upvote 0

Forum statistics

Threads
1,214,644
Messages
6,120,709
Members
448,983
Latest member
Joaquim_Baptista

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top