title: The way to speed up multiple ElseIf queries in VBA
body: <p>I've been working on this code all day and have finally gotten everything to work perfectly. The only problem, is that the code does run pretty slow. Considering that it will be used on a workbook with thousands of rows I would like to change that. I am extremely new to vba so there is probably stuff in here that is wrong or seems like a bad shortcut. I think I added a couple of ways that could speed it up but i didnt know if anything else could be done.</p>
  <pre><code>Option Explicit
  
  Public Sub CL_NC()
  
  Dim lZeile  As Long
  
  With ThisWorkbook.Worksheets(&quot;Gesamt&quot;)
  
  For lZeile = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
    
  If InStr(.Range(&quot;D&quot; &amp; lZeile).Value, &quot;Malaysia&quot;) &gt; 0 And (InStr(.Range(&quot;B&quot; &amp; lZeile).Value, &quot;ISO 9001&quot;) &gt; 0 Or InStr(.Range(&quot;B&quot; &amp; lZeile).Value, &quot;ISO 14001&quot;) &gt; 0 Or InStr(.Range(&quot;B&quot; &amp; lZeile).Value, &quot;BS OHSAS 18001&quot;) &gt; 0 Or InStr(.Range(&quot;B&quot; &amp; lZeile).Value, &quot;ISO 45001&quot;) &gt; 0 Or InStr(.Range(&quot;B&quot; &amp; lZeile).Value, &quot;ISO 50001&quot;) &gt; 0) Then
          .Range(&quot;G&quot; &amp; lZeile).Value = &quot;Malaysia IMS CL&quot;
  ElseIf InStr(.Range(&quot;D&quot; &amp; lZeile).Value, &quot;Malaysia&quot;) &gt; 0 And InStr(.Range(&quot;C&quot; &amp; lZeile).Value, &quot;IMS&quot;) &gt; 0 Then
          .Range(&quot;G&quot; &amp; lZeile).Value = &quot;Malaysia IMS NC&quot;
  ElseIf InStr(.Range(&quot;D&quot; &amp; lZeile).Value, &quot;Malaysia&quot;) &gt; 0 Then
          .Range(&quot;G&quot; &amp; lZeile).Value = &quot;Malaysia &quot; &amp; .Range(&quot;C&quot; &amp; lZeile).Value &amp; &quot; NC&quot;
  
  ElseIf InStr(.Range(&quot;D&quot; &amp; lZeile).Value, &quot;Indonesien&quot;) &gt; 0 And (InStr(.Range(&quot;B&quot; &amp; lZeile).Value, &quot;ISO 9001&quot;) &gt; 0 Or InStr(.Range(&quot;B&quot; &amp; lZeile).Value, &quot;ISO 14001&quot;) &gt; 0 Or InStr(.Range(&quot;B&quot; &amp; lZeile).Value, &quot;BS OHSAS 18001&quot;) &gt; 0 Or InStr(.Range(&quot;B&quot; &amp; lZeile).Value, &quot;ISO 45001&quot;) &gt; 0 Or InStr(.Range(&quot;B&quot; &amp; lZeile).Value, &quot;ISO 50001&quot;) &gt; 0) Then
          .Range(&quot;G&quot; &amp; lZeile).Value = &quot;Indonesien IMS CL&quot;
  ElseIf InStr(.Range(&quot;D&quot; &amp; lZeile).Value, &quot;Indonesien&quot;) &gt; 0 And InStr(.Range(&quot;C&quot; &amp; lZeile).Value, &quot;IMS&quot;) &gt; 0 Then
          .Range(&quot;G&quot; &amp; lZeile).Value = &quot;Indonesien IMS NC&quot;
  ElseIf InStr(.Range(&quot;D&quot; &amp; lZeile).Value, &quot;Indonesien&quot;) &gt; 0 Then
          .Range(&quot;G&quot; &amp; lZeile).Value = &quot;Indonesien &quot; &amp; .Range(&quot;C&quot; &amp; lZeile).Value &amp; &quot; NC&quot;
  
  
          
       Else
          .Range(&quot;G&quot; &amp; lZeile).Value = &quot;&quot;
       End If
    Next lZeile
  End With
  
  End Sub
  </code></pre>
  <p>This is how the table looks right now. Column G should show the VBA result.</p>
  <p><a href="https://i.stack.imgur.com/ix6j2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ix6j2.png" alt="enter image description here" /></a></p>
  <p>@Damian: Dear Damian, your help here was really fantastic. But I am afraid, I need your assistance once again… I have some countries in the table (i.e. Bulgaria“) which obey to the same rules as Malaysia &amp; Indonesia, but additionally they should also be marked as „Bulgaria Food CL“ when the entry in the column B = „ISO 22000“ (in this case the entry in column C is „Food“). Can it also be solved within this code? Thank you so much in advance!</p>