title: Why does my search function take much longer after rearranging it to save a few checks?
body: <p>I wrote a Sub which looks for the Search Terms from one sheet, and captures each line from a big array of strings which contains one of those terms (printing it to another sheet).</p>
  <p>Originally I had it written like this:</p>
  <ul>
  <li>For each Search Term, look at each line in the file and print each match.</li>
  </ul>
  <p>It worked, but it meant that if one line contained multiple Search Terms, it would get printed as many times.  I wanted to eliminate that, just printing the line once if it contained at least one Search Term.  So, I rearranged the code to work like this:</p>
  <ul>
  <li>For each line in the file, look at each Search Term and print the line if it matches.</li>
  <li>(and if you do get a match, skip the remaining Search Terms for that line)</li>
  </ul>
  <p>If anything, this way should be slightly more efficient than the original, I thought...
  And yet, after I rearranged the code, it takes <strong>much longer</strong> to execute.</p>
  <p>I guess this means that, though I feel as though I'm doing the same things in a different order, and even saving some unnecessary checks, I must be somehow compounding the parts that take the longest.  Can anyone shed some light and help me understand how I've made the code so much worse?</p>
  <p>Here's what the two versions of the relevant code segment look like:</p>
  <p><strong>Original way:</strong></p>
  <pre><code>searchTermRow = firstSearchTermRow
  While ActiveWorkbook.Sheets(&quot;SearchTerms&quot;).Cells(searchTermRow, 1) &lt;&gt; &quot;&quot;
      term = ActiveWorkbook.Sheets(&quot;SearchTerms&quot;).Cells(searchTermRow, 1)
      For j = 1 To UBound(bigStringArray) - 1
          counter = counter + 1
          If (InStr(bigStringArray(j), term) &lt;&gt; 0) Then
              Cells(resultsRow, 1).Value = bigStringArray(j)
              resultsRow = resultsRow + 1
          End If
      Next j
      searchTermRow = searchTermRow + 1
  Wend
  </code></pre>
  <p><strong>Rearranged:</strong></p>
  <pre><code>For j = 1 To UBound(bigStringArray) - 1
      searchTermRow = firstSearchTermRow
      Do While ActiveWorkbook.Sheets(&quot;SearchTerms&quot;).Cells(searchTermRow, 1) &lt;&gt; &quot;&quot;
          term = ActiveWorkbook.Sheets(&quot;SearchTerms&quot;).Cells(searchTermRow, 1)
          counter = counter + 1
          If (InStr(bigStringArray(j), term) &lt;&gt; 0) Then
              Cells(resultsRow, 1).Value = bigStringArray(j)
              resultsRow = resultsRow + 1
              Exit Do
          End If
          searchTermRow = searchTermRow + 1
      Loop
  Next j
  </code></pre>
  <p>bigStringArray is a large array of strings which consists of every line from a file.
  counter is just there so that I could count how many checks were done.</p>