VBA Backreferencing using Regular Expressions Find and Replace

Question

I want to standardize the formatting of some text within a Word document using VBA and regEx. For example, my document would contain text strings such as "Qty #", "Qty (#)", "QTY(#)", "qty of (#)", etc where # is an integer value from 0-99. My objective is to standardize these strings into the format: "QTY (#)".

Below is the code I have attempted; however, instead of backreferencing the number it inserts a literal "$1" within the parentheses. I've done some research and it appears that the Range.Find doesn't support backreferences. Is there another method that works?

Sub StandardizeQuantityFormat()
    Dim regEx As Object
    Set regEx = CreateObject("VBScript.RegExp")
   
    ' Define the regular expression pattern to match various quantity formats.
    regEx.Pattern = "Qty\s+(\d{1,2})|Qty\s+\((\d{1,2})\)|QTY\((\d{1,2})\)|qty of\s+\((\d{1,2})\)"
    regEx.IgnoreCase = True
    regEx.Global = True
   
    ' Define the desired format to replace found patterns.
    Dim replacementFormat As String
    replacementFormat = "QTY ($1)"
   
    ' Access the document's content.
    Dim docContent As Range
    Set docContent = ActiveDocument.Content
   
    ' Execute the Find and Replace using the RegExp.
    Dim match As Object
    For Each match In regEx.Execute(docContent.Text)
        With docContent.Find
            .Text = match.Value ' The text to find
            .Replacement.Text = replacementFormat
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False ' Make sure to set Format to False to avoid formatting issues
            .Execute Replace:=wdReplaceAll
        End With
    Next match

    Set regEx = Nothing
    MsgBox "Quantity formats have been standardized."
End Sub

Answer 1

It appears there may have been a misunderstanding in utilizing the Word replacement feature instead of the RegExp (Regular Expression) replacement method.

Option Explicit

Sub StandardizeQuantityFormat()
    Dim regEx As Object
    Set regEx = CreateObject("VBScript.RegExp")
   
    ' Define the regular expression pattern to match various quantity formats.
    regEx.Pattern = "Qty\s*(?:of\s+)*\(*(\d{1,2})\)*"
    regEx.IgnoreCase = True
    regEx.Global = True
     
    ' Access the document's content.
    Dim docContent As Range
    Set docContent = ActiveDocument.Content
   
    ' Execute the Find and Replace using the RegExp.
    Dim match As Object
    For Each match In regEx.Execute(docContent.Text)
        With docContent.Find
            .ClearFormatting
            .Text = match.Value ' The text to find
            .Replacement.ClearFormatting
            .Replacement.Text = regEx.Replace(match.Value, "QTY ($1)")
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False ' Make sure to set Format to False to avoid formatting issues
            .Execute Replace:=wdReplaceAll
        End With
    Next match

    Set regEx = Nothing
    MsgBox "Quantity formats have been standardized."
End Sub