title: Getting wildcards to work in find and replace function in VBA macro for Microsoft Word
body: <p>I have a VBA macro for Microsoft Word that I am trying to improve.  </p>
  
  <p>The purpose of the macro is to bold and italicize all words in a document that match the search terms in the first table of the document.</p>
  
  <p>The problem is the search terms include wildcards which are the following:</p>
  
  <p>the hyphen "-": between letters a wildcard for either a space or a period</p>
  
  <p>asterisk "&amp;": (the site is not letting me put in asterisks as this is the markdown for italicize, so I'll put in the &amp; symbol instead to get around the filters)  a wildcard for any number of characters at the beginning of a word or at the end.  Unlike normal programming languages though, when it is used in the middle of the word it needs to be combined with the hyphen to be a wildcard for a range of characters. For example "th&amp;-e" would pick up "there" while "th&amp;e" would not.</p>
  
  <p>question mark "?":  wildcard for a single character</p>
  
  <p>What I am doing so far is just testing for these characters and if they are present I either lop them off in the case of the asterisk, or I alert the user that they have to search for the word manually.  Not ideal :-P</p>
  
  <p>I have tried the .MatchWildcard property in VBA but have not yet gotten it to work.  I have a feeling it has something to do with the replacement text, not the search text.</p>
  
  <p>A working macro will take the following as its input (the first row is intentionally ignored and the second column is the one with the target search terms):</p>
  
  <p>Imagine this in a table all in the second column (as the html allowed here doesn't allow tr and td etc)</p>
  
  <blockquote>
    <p>First row:  Word<br>
    Second row: Search<br>
    Third row: &amp;earch1<br>
    Fourth row: Search2&amp;<br>
    Fifth row: S-earch3<br>
    Sixth row: S?arch4<br>
    Seventh row: S&amp;-ch5  </p>
  </blockquote>
  
  <p>And it will search the document and replace with bold and italicized content like so:</p>
  
  <p><strong><em>Search Search1 Search2 Search3 Search4 Search5</em></strong></p>
  
  <p>Note: S-earch3 could also pick up S.earch3 and replace with Search3</p>
  
  <p>As one might assume the search terms will usually not be right next to each other - the macro should find all instances.</p>
  
  <p>I will include my attempted but nonfunctional code as well after the first working macro.</p>
  
  <p>The code for the working macro will be on pastebin for a month from today, which is 9/17/09, at the following <a href="http://pastebin.com/m7ee9165f" rel="nofollow noreferrer">url</a>.</p>
  
  <p>Thanks again for any thoughts and help you might have to offer!</p>
  
  <p>Sara</p>
  
  <p>Working VBA Macro:</p>
  
  <pre><code>Sub AllBold()
  
  Dim tblOne As Table
  
  Dim celTable As Cell
  
  Dim rngTable As Range
  
  Dim intCount As Integer
  
  Dim celColl As Cells
  
  Dim i As Integer
  
  Dim rngLen As Integer
  
  Dim bolWild As Boolean
  
  Dim strWild As String
  
  
  Set tblOne = ActiveDocument.Tables(1)
  
  intCount = tblOne.Columns(2).Cells.Count
  
  Set celColl = tblOne.Columns(2).Cells
  
  strWild = ""
  
  For i = 1 To intCount
  
      If i = 1 Then
  
      i = i + 1
  
      End If
  
      Set celTable = ActiveDocument.Tables(1).Cell(Row:=i, Column:=2)
  
      Set rngTable = ActiveDocument.Range(Start:=celTable.Range.Start, _
          End:=celTable.Range.End - 1)
  
      rngLen = Len(rngTable.Text)
  
      bolWild = False
  
      If (Mid(rngTable.Text, rngLen, 1) = "&amp;") Then 'remember to replace &amp; with asterisk!'
  
      rngTable.SetRange Start:=rngTable.Start, End:=rngTable.End - 1
  
      End If
  
      If (Mid(rngTable.Text, 1, 1) = "&amp;") Then 'remember to replace &amp; with asterisk!'
  
      rngTable.SetRange Start:=rngTable.Start + 1, End:=rngTable.End
  
      End If
  
      If InStr(1, rngTable.Text, "-", vbTextCompare) &gt; 0 Then
  
      strWild = strWild + rngTable.Text + Chr$(13)
  
      bolWild = True
  
      End If
  
      If InStr(1, rngTable.Text, "?", vbTextCompare) &gt; 0 Then
  
      strWild = strWild + rngTable.Text + Chr$(13)
  
      bolWild = True
  
      End If
  
      If (bolWild = False) Then
  
          Dim oRng As Word.Range
  
              Set oRng = ActiveDocument.Range
  
              With oRng.Find
  
              .ClearFormatting
  
              .Text = rngTable.Text
  
              With .Replacement
  
              .Text = rngTable.Text
  
              .Font.Bold = True
  
              .Font.Italic = True
  
              End With
  
              .Execute Replace:=wdReplaceAll
  
      End With
  
      End If
  
  Next
  
  If bolWild = True Then
  
  MsgBox ("Please search the following strings with - or ? manually:" + Chr$(13) + strWild)
  
  End If
  
  End Sub
  </code></pre>
  
  <p>Attempted Nonfunctional VBA Macro:</p>
  
  <pre><code>Sub AllBoldWildcard()
  
  Dim tblOne As Table
  
  Dim celTable As Cell
  
  Dim rngTable As Range
  
  Dim intCount As Integer
  
  Dim celColl As Cells
  
  Dim i As Integer
  
  Dim rngLen As Integer
  
  Dim bolWild As Boolean
  
  Dim strWild As String
  
  Dim strWildcard As String
  
  
  Set tblOne = ActiveDocument.Tables(1)
  
  intCount = tblOne.Columns(2).Cells.Count
  
  Set celColl = tblOne.Columns(2).Cells
  
  strWild = ""
  
  For i = 1 To intCount
  
      If i = 1 Then
  
      i = i + 1
  
      End If
  
      Set celTable = ActiveDocument.Tables(1).Cell(Row:=i, Column:=2)
  
      Set rngTable = ActiveDocument.Range(Start:=celTable.Range.Start, _
          End:=celTable.Range.End - 1)
  
      rngLen = Len(rngTable.Text)
  
      bolWild = False
  
      If (Mid(rngTable.Text, 1, 1) = "&amp;") Then 'remember to replace &amp; with asterisk!'
  
      rngTable.SetRange Start:=rngTable.Start + 1, End:=rngTable.End
  
      End If
  
      If InStr(1, rngTable.Text, "&amp;", vbTextCompare) &gt; 0 Then 'remember to replace &amp; with asterisk!'
  
      strWildcard = rngTable.Text
  
      rngTable.Text = Replace(rngTable.Text, "&amp;", "", 1) 'remember to replace &amp; with asterisk!'
  
      bolWild = True
  
      End If
  
      If InStr(1, rngTable.Text, "-", vbTextCompare) &gt; 0 Then
  
      strWildcard = Replace(rngTable.Text, "-", "[.-]", 1)
  
      bolWild = True
  
      End If
  
      If InStr(1, rngTable.Text, "?", vbTextCompare) &gt; 0 Then
  
      strWild = strWild + rngTable.Text + Chr$(13)
  
      strWildcard = Replace(rngTable.Text, "?", "_", 1)
  
  
      bolWild = True
  
      End If
  
      If (bolWild = False) Then
  
          Dim oRng As Word.Range
  
              Set oRng = ActiveDocument.Range
  
              With oRng.Find
  
              .ClearFormatting
  
              .Text = strWildcard
  
              .MatchAllWordForms = False
  
              .MatchSoundsLike = False
  
              .MatchFuzzy = False
  
              .MatchWildcards = True
  
  
              With .Replacement
  
              .Text = rngTable.Text
  
              .Font.Bold = True
  
              .Font.Italic = True
  
              End With
  
              .Execute Replace:=wdReplaceAll
  
      End With
  
      End If
  
  Next
  
  '    If bolWild = True Then'
  
  '    MsgBox ("Please search the following strings with - or ? manually:" + Chr$(13) + strWild)'
  
  '    End If'
  
  End Sub
  </code></pre>