I am working with VBA in an xlsx document, and I am attempting to alter a separate rtf document to replace the words in a specific range between two tags on the document. I have managed to replace all the instances of any word across the entire document, and have also managed to retrieve the text in the range between the tags. If I try to set a range however, I end up getting either a type mismatch on the range, or an error saying the object variable or With block variable not set.
Public Sub WordFindAndReplaceTEST()
Dim ws As Worksheet, msWord As Object
Dim firstTerm As String
Dim secondTerm As String
Dim documentText As String
Dim myRange As Range
Dim startPos As Long 'Stores the starting position of firstTerm
Dim stopPos As Long 'Stores the starting position of secondTerm based on first term's location
Dim nextPosition As Long 'The next position to search for the firstTerm
nextPosition = 1
firstTerm = "<Tag2.1.1>"
secondTerm = "</Tag2.1.1>"
On Error Resume Next
Set msWord = GetObject(, "Word.Application")
If wrdApp Is Nothing Then
Set msWord = CreateObject("Word.Application")
End If
On Error GoTo 0
Set ws = ActiveSheet
With msWord
.Visible = True
.Documents.Open "C:\Users\user\Desktop\ReportTest\ReportDoc.rtf"
.Activate
'Get all the document text and store it in a variable.
documentText = .ActiveDocument.Content
'Loop documentText till you can't find any more matching "terms"
Do Until nextPosition = 0
startPos = InStr(nextPosition, documentText, firstTerm, vbTextCompare)
stopPos = InStr(startPos, documentText, secondTerm, vbTextCompare)
nextPosition = InStr(stopPos, documentText, firstTerm, vbTextCompare)
Loop
Set myRange = Nothing
myRange.SetRange Start:=startPos, End:=stopPos 'Error thrown here
MsgBox .ActiveDocument.Range(startPos, stopPos) 'Successfully returns range as string
With .ActiveDocument.Content.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "toReplace"
.Replacement.Text = "replacementText"
.Forward = True
.Wrap = 1
.format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute Replace:=2
End With
'Overrides original
'.Quit SaveChanges:=True
End With
End Sub
I initially tried to assign myRange without setting it first. I have tried moving the scope of myRange and the declaration of myRange. I have tried to Set myRange = .ActiveDocument.Range or .ActiveDocument.Content.
I have also tried replacing the line: With .ActiveDocument.Content.Find with With .ActiveDocument.myRange(startPos, stopPos).Find
Anything I try throws an error, and I have tried looking around for similar issues and reading the VBA docs but have yet to figure out where the issue lies.