title: Find Keyword(s) in UsedRange - VBA (Excel)
body: <p>I am trying to look for a Keyword (e.g. Quantity, Qty, Qty., ... , etc) within range (in this case used range) but I am experiencing a problem; the search is slow because I am using <code>InStr</code> and <code>For each cell in UsedRange</code></p>
  
  <p>Method 1 (Slow):</p>
  
  <pre><code>Private Function GetQtyColFromBOQ(thisBOQ As Worksheet) As Range
      Dim QtyWord(5), QtyWordG, Delim As String
      Dim cl As Range
  
      If thisBOQ Is Nothing Then Set thisBOQ = ActiveSheet
  
      Delim = "|#|"
  
      QtyWord(0) = "Quantity"
      QtyWord(1) = "Qty"
      QtyWord(2) = "Qty."
      QtyWord(3) = "Qnty"
      QtyWord(4) = "Qnty."
  
      QtyWordG = Delim &amp; Join(QtyWord, Delim)
  
      For Each cl In thisBOQ.UsedRange
          If InStr(1, QtyWordG, Delim &amp; cl.Value &amp; Delim, vbTextCompare) Then
              Set GetQtyColumnFromBOQ = cl ' function return
              Exit For
          End If
      Next
  End Function
  </code></pre>
  
  <p>Method 2 (Not working): No match found</p>
  
  <pre><code>Private Function GetQtyColFromBOQ(thisBOQ As Worksheet) As Range
      Dim QtyWord(5) As String
  
      If thisBOQ Is Nothing Then Set thisBOQ = ActiveSheet
  
      QtyWord(0) = "Quantity"
      QtyWord(1) = "Qty"
      QtyWord(2) = "Qty."
      QtyWord(3) = "Qnty"
      QtyWord(4) = "Qnty."
  
  
      Dim i As Integer
      For i = 0 To 4
        Set GetQtyColumnFromBOQ = thisBOQ.UsedRange.Find(QtyWord(i), LookAt:=xlWhole)
      Next i
  End Function
  </code></pre>
  
  <p>What could possibly be wrong ?</p>