title: How can I work around the Find(what:=) character limitation in excel vba
body: <p>I just released an Excel Add-In in my department today that I've been working on for the last 2+ months that checks for about 30 validation errors. I have the error trapping handled in all situations (as it appears right now), but I received a horrible wake-up call today as I received automatic emails (a feature I built into the error handling) for two vital bugs. The first of which is below, the second I will post separately.</p>
  
  <p>The first bug has to do with the <code>.Find what:=</code> character limitation</p>
  
  <p>The Sub that is throwing this error is as follows</p>
  
  <pre><code>'Converts Upcharge columns to all uppercase as a safety protocol,
  'Checks for colons in option names and removes them from the Option Name column and in the
  'upcharge columns if any upcharges correspond to that option name for the particular product.
  Private Sub colOpNaCheck()
  On Error GoTo ErrHandler
  Application.StatusBar = "(11/16) Checking option names for colons"
  
      Dim rng As Range, aCell As Range, uRng1 As Range, uRng2 As Range, uCell As Range, tempC As Range
      Dim endRange As Long
      Dim opName As String, opName2 As String
      Dim xid As String
  
      endRange = ActiveSheet.Range("A" &amp; Rows.count).End(xlUp).Row
  
      Set rng = ActiveSheet.Range("W1:W" &amp; endRange)
  
      Set aCell = rng.Find(What:=":", LookIn:=xlValues, _
                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                  MatchCase:=False, SearchFormat:=False)
  
      If Not aCell Is Nothing Then
          'Add colon to beginning and end of string to ensure we only find and replace the right
          'portion over in upcharge column
          opName = ":" &amp; aCell.Value &amp; ":"
          'Correct the value in column W
          aCell = Replace(ActiveSheet.Range("W" &amp; aCell.Row).Value, ":", "")
          'Set corrected value (sans-colon) to opName2 and add colon to beginning and
          'end of string
          opName2 = ":" &amp; aCell.Value &amp; ":"
          'Note the XID of the current row so we can ensure we look for the right upcharge
          xid = ActiveSheet.Range("A" &amp; aCell.Row).Value
          'We have the option name and the xid associated with it
          'Now we have to do a find in the upcharges column to see if we find the opName
          'Then we do an if statement and only execute if the the Column A XID value matches
          'the current xid value we have now
          Set uRng1 = ActiveSheet.Range("CT1:CT" &amp; endRange)
          Set uRng2 = ActiveSheet.Range("CU1:CU" &amp; endRange)
  
          'Convert uRng1 &amp; uRng2 to all uppercase just to make sure they will be detected when using Find
          ActiveSheet.Range(uRng1, uRng2).Select
          For Each tempC In Selection
              'If cell does not contain an Error AND is not missing a value/is empty AND cell is not already all uppercase
              'AND Row is not 1. All of these checks help us save on processing time
              If Not IsError(tempC) And Not IsMissing(tempC) And tempC.Value &lt;&gt; UCase(tempC.Value) And tempC.Row &lt;&gt; 1 Then
                  tempC.Value = UCase(tempC)
              End If
          Next tempC
  
          'Set uCell to the first instance of opName
          Set uCell = uRng1.Find(What:=UCase(opName), LookIn:=xlValues, _
                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                  MatchCase:=False, SearchFormat:=False)
          'If there is an instance of opName and uCell has the value check if the xid matches
          'to ensure we 're changing the right upcharge
          Do
              'Check the upcharges
              Set uCell = uRng1.Find(What:=UCase(opName), LookIn:=xlValues, _
                      LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                      MatchCase:=False, SearchFormat:=False)
              If Not uCell Is Nothing Then
                  Do While ActiveSheet.Range("A" &amp; uCell.Row).Value = xid
                      Set uCell = uRng1.Find(What:=UCase(opName), LookIn:=xlValues, _
                              LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                              MatchCase:=False, SearchFormat:=False)
                      'Correct the value in column CT
                      If Not uCell Is Nothing Then
                          If ActiveSheet.Range("A" &amp; uCell.Row).Value = xid Then
                              uCell = Replace(UCase(ActiveSheet.Range("CT" &amp; uCell.Row).Value), UCase(opName), UCase(opName2))
                          Else
                              Exit Do
                          End If
                      Else
                          Exit Do
                      End If
                  Loop
              End If
  
              'Now we look in upcharge_criteria_2 column
              Set uCell = uRng2.Find(What:=UCase(opName), LookIn:=xlValues, _
                      LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                      MatchCase:=False, SearchFormat:=False)
              If Not uCell Is Nothing Then
                  Do While ActiveSheet.Range("A" &amp; uCell.Row).Value = xid
                      Set uCell = uRng2.Find(What:=UCase(opName), LookIn:=xlValues, _
                              LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                              MatchCase:=False, SearchFormat:=False)
                      'Correct the value in column CU
                      If Not uCell Is Nothing Then
                          If ActiveSheet.Range("A" &amp; uCell.Row).Value = xid Then
                              uCell = Replace(UCase(ActiveSheet.Range("CU" &amp; uCell.Row).Value), UCase(opName), UCase(opName2))
                          Else
                              Exit Do
                          End If
                      Else
                          Exit Do
                      End If
                  Loop
              End If
          'Exit the Do Statement since we've fixed all the upcharges for this particular Option Name
          Exit Do
          Loop
  
          Do
              'Check for Options
              Set aCell = rng.Find(What:=":", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                  MatchCase:=False, SearchFormat:=False)
              If Not aCell Is Nothing Then
                  'Add colon to beginning and end of string to ensure we only find and
                  'replace the right portion over in upcharge column
                  opName = ":" &amp; aCell.Value &amp; ":"
                  'Correct the value in column W (Option_Name)
                  aCell = Replace(ActiveSheet.Range("W" &amp; aCell.Row).Value, ":", "")
                  'Set corrected value (sans-colon) to opName2 and add colon to
                  'beginning and end of string
                  opName2 = ":" &amp; aCell.Value &amp; ":"
                  'Note the XID of the current row so we can ensure we look for the right upcharge
                  xid = ActiveSheet.Range("A" &amp; aCell.Row).Value
                  Do
                      'Check the upcharges
                      Set uCell = uRng1.Find(What:=UCase(opName), LookIn:=xlValues, _
                              LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                              MatchCase:=False, SearchFormat:=False)
                      If Not uCell Is Nothing Then
                          Do While ActiveSheet.Range("A" &amp; uCell.Row).Value = xid
                              Set uCell = uRng1.Find(What:=UCase(opName), LookIn:=xlValues, _
                                      LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                      MatchCase:=False, SearchFormat:=False)
                                  'Correct the value in column CT
                              If Not uCell Is Nothing Then
                                  If ActiveSheet.Range("A" &amp; uCell.Row).Value = xid Then
                                      uCell = Replace(UCase(ActiveSheet.Range("CT" &amp; uCell.Row).Value), UCase(opName), UCase(opName2))
                                  Else
                                      Exit Do
                                  End If
                              Else
                                  Exit Do
                              End If
                          Loop
                      End If
  
                      'Now we look in upcharge_criteria_2 column
                      Set uCell = uRng2.Find(What:=UCase(opName), LookIn:=xlValues, _
                              LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                              MatchCase:=False, SearchFormat:=False)
                      If Not uCell Is Nothing Then
                          Do While ActiveSheet.Range("A" &amp; uCell.Row).Value = xid
                              Set uCell = uRng2.Find(What:=UCase(opName), LookIn:=xlValues, _
                                      LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                      MatchCase:=False, SearchFormat:=False)
                              'Correct the value in column CU
                              If Not uCell Is Nothing Then
                                  If ActiveSheet.Range("A" &amp; uCell.Row).Value = xid Then
                                      uCell = Replace(UCase(ActiveSheet.Range("CU" &amp; uCell.Row).Value), UCase(opName), UCase(opName2))
                                  Else
                                      Exit Do
                                  End If
                              Else
                                  Exit Do
                              End If
                          Loop
                      End If
                      'Exit the Do Statement since we've fixed all the upcharges for this particular Option Name
                      Exit Do
                  Loop
              Else
                  Exit Do
              End If
          Loop
      End If
  
      Exit Sub
  ErrHandler: 'This raises the error back to the parent Sub where my Email on error handler records the error
      Err.Raise Err.Number, "colOpNaCheck", Err.Description
  End Sub
  </code></pre>
  
  <p>The <code>Error 13: Type Mismatch</code> error occurs on this line</p>
  
  <pre><code>'Set uCell to the first instance of opName
              Set uCell = uRng1.Find(What:=UCase(opName), LookIn:=xlValues, _
                      LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                      MatchCase:=False, SearchFormat:=False)
  </code></pre>
  
  <p>When this error occurs the value of <code>opName</code>is </p>
  
  <pre><code>"Order Changes. Any changes made to orders after receipt of initial PO must be made in writing via e-mail or fax. Each change will be billed. All changes made the same day as order shipment will be billed. All changes made the same day as order shipment must be received before 3:00 pm EST."
  </code></pre>
  
  <p>And the values it should be finding/replacing reside in the middle of these two strings</p>
  
  <pre><code>1. "PROP:ORDER CHANGES. ANY CHANGES MADE TO ORDERS AFTER RECEIPT OF INITIAL PO MUST BE MADE IN WRITING VIA E-MAIL OR FAX. EACH CHANGE WILL BE BILLED. ALL CHANGES MADE THE SAME DAY AS ORDER SHIPMENT WILL BE BILLED. ALL CHANGES MADE THE SAME DAY AS ORDER SHIPMENT MUST BE RECEIVED BEFORE 3:00 PM EST.:EACH CHANGE"
  2. "PROP:ORDER CHANGES. ANY CHANGES MADE TO ORDERS AFTER RECEIPT OF INITIAL PO MUST BE MADE IN WRITING VIA E-MAIL OR FAX. EACH CHANGE WILL BE BILLED. ALL CHANGES MADE THE SAME DAY AS ORDER SHIPMENT WILL BE BILLED. ALL CHANGES MADE THE SAME DAY AS ORDER SHIPMENT MUST BE RECEIVED BEFORE 3:00 PM EST.:ALL CHANGES MADE THE SAME DAY AS ORDER SHIPMENT"
  </code></pre>
  
  <p><strong>My Questions:</strong></p>
  
  <ol>
  <li>How can I work around this <code>.Find what:=</code> limitation while making as few adjustments as possible to my code?</li>
  <li>Could you help show me how I could implement the workaround method(s)?</li>
  </ol>
  
  <p><strong>Update: Almost There</strong></p>
  
  <p>Thanks to Tim's advice and method I now have the following code</p>
  
  <pre><code>'Converts Upcharge columns to all uppercase as a safety protocol,
  'Checks for colons in option names and removes them from the Option Name column and in the
  'upcharge columns if any upcharges correspond to that option name for the particular product.
  Private Sub colOpNaCheck()
  
  'Application.StatusBar = "(11/16) Checking option names for colons"
  
      Dim onRng As Range, uRng1 As Range, uRng2 As Range, tempC As Range
      Dim aCell As Collection, uCell As Collection, el, el2, el3
      Dim endRange As Long
      Dim opName As String, opName2 As String, xid As String
  
      endRange = ActiveSheet.Range("A" &amp; Rows.Count).End(xlUp).Row
  
      Set onRng = ActiveSheet.Range("W1:W" &amp; endRange)
      Set uRng1 = ActiveSheet.Range("CT1:CT" &amp; endRange)
      Set uRng2 = ActiveSheet.Range("CU1:CU" &amp; endRange)
  
      Set aCell = FindAllMatches(onRng, ":")
  
      If Not aCell Is Nothing Then
      'Convert uRng1 &amp; uRng2 to all uppercase
  '            ActiveSheet.Range(uRng1, uRng2).Select
  '            For Each tempC In Selection
  '                'If cell does not contain an Error AND is not missing a value/is empty AND cell is not already all uppercase
  '                'AND Row is not 1. All of these checks help us save on processing time
  '                If Not IsError(tempC) And Not IsMissing(tempC) And tempC.Value &lt;&gt; UCase(tempC.Value) And tempC.Row &lt;&gt; 1 Then
  '                    tempC.Value = UCase(tempC)
  '                End If
  '            Next tempC
          For Each el In aCell
              'Add colon to beginning and end of string to ensure we only find and replace the right
              'portion over in upcharge column
              opName = ":" &amp; el.Value &amp; ":"
              'Correct the value in column W
              el.Value = Replace(ActiveSheet.Range("W" &amp; el.Row).Value, ":", "")
              'Set corrected value (sans-colon) to opName2 and add colon to beginning and
              'end of string
              opName2 = ":" &amp; el.Value &amp; ":"
              'Note the XID of the current row so we can ensure we look for the right upcharge
              xid = ActiveSheet.Range("A" &amp; el.Row).Value
              'We have the option name and the xid associated with it
              'Now we have to do a find in the upcharges column to see if we find the opName
              'Then we do an if statement and only execute if the Column A XID value matches
              'the current xid value we have now
  
              'set all instances of opName to uCell
              Set uCell = FindAllMatches(uRng1, opName)
              If Not uCell Is Nothing Then
                  For Each el2 In uCell
                  'Correct the value in column CT
                  el2.Value = Replace(UCase(ActiveSheet.Range("CT" &amp; el2.Row).Value), UCase(opName), UCase(opName2))
                  Next el2
              End If
  
              Set uCell = FindAllMatches(uRng2, opName)
              If Not uCell Is Nothing Then
                  For Each el3 In uCell
                  'Correct the value in column CT
                  el3.Value = Replace(UCase(ActiveSheet.Range("CT" &amp; el3.Row).Value), UCase(opName), UCase(opName2))
                  Next el3
              End If
      Next el
  
  End If
  
  End Sub
  
  Function FindAllMatches(rng As Range, txt As String) As Collection
      Dim rv As New Collection, f As Range, addr As String, txtSrch As String
      Dim IsLong As Boolean
  
      IsLong = Len(txt) &gt; 250
      txtSrch = IIf(IsLong, Left(txt, 250), txt)
  
      Set f = rng.Find(what:=txtSrch, lookat:=xlPart, MatchCase:=False)
      Do While Not f Is Nothing
          If f.Address(False, False) = addr Then Exit Do
          If Len(addr) = 0 Then addr = f.Address(False, False)
          'check for the *full* value
          If InStr(f.Value, txt) &gt; 0 Then rv.Add f
          Set f = rng.FindNext(after:=f)
      Loop
      Set FindAllMatches = rv
  End Function
  </code></pre>
  
  <p>However, when I use his function to find all the instances over in the upcharge column with these lines</p>
  
  <pre><code>'set all instances of opName to uCell
   Set uCell = FindAllMatches(uRng1, opName)
   If Not uCell Is Nothing Then
   ...
  </code></pre>
  
  <p>uCell is always displaying No Variables in the Watch window, even with the value I stated above. What am I doing wrong? Or does the <code>FindAllMatches</code> function need to be adjusted?</p>