title: Optimize double loop Excel VBA
body: <p>I would like to optimize many loops which compare 2 tables in my code.</p>
  
  <p>Indeed, the execution time is very long because the 2 tables have about 1500 rows to compare. So, a double loop at the end of the code is just doing 1500*1500 actions. So 2 250 000 actions is really too many.</p>
  
  <p>Hope you will be able to help me, I didn't find the trick to do something else...</p>
  
  <p>Here is the code :</p>
  
  <pre><code>'********************This code compare if some values are in the first table and not in the second one and then in the second one and not in the first one with 2 loops********************
  
  
  x = DL_COMPARATIF + 4
  
  For t = 2 To DL_COMPARATIF
  
  If Application.WorksheetFunction.CountIf(Sheets("AFFRETEMENTS EN COURS").Range("T:T"), Sheets("COMPARATIF").Range("C" &amp; t)) = 0 Then
  
          x = x + 1
  
          For k = 1 To 17
  
              Sheets("COMPARATIF").Cells(x, k) = Sheets("COMPARATIF").Cells(t, k)
  
          Next k
  
          Sheets("COMPARATIF").Range("R" &amp; x) = "L'OT ne figure pas dans Excel."
  
          Sheets("COMPARATIF").Range("A" &amp; x &amp; ":S" &amp; x).Interior.Color = RGB(221, 235, 247)
  
          Erreur_mois = True
  
  End If
  
  Next t
  
  For t = 3 To DL_AFFRETEMENT
  
      If Application.WorksheetFunction.CountIf(Sheets("COMPARATIF").Range("C:C"), Sheets("AFFRETEMENTS EN COURS").Range("T" &amp; t)) = 0 Then
  
          If Sheets("AFFRETEMENTS EN COURS").Range("V" &amp; t) = "Affrété &amp; faxé" Then
  
              x = x + 1
  
              Sheets("COMPARATIF").Range("A" &amp; x) = Sheets("AFFRETEMENTS EN COURS").Range("B" &amp; t) 'client
              Sheets("COMPARATIF").Range("C" &amp; x) = Sheets("AFFRETEMENTS EN COURS").Range("T" &amp; t) 'Numéro d'OT
              Sheets("COMPARATIF").Range("E" &amp; x) = Sheets("AFFRETEMENTS EN COURS").Range("S" &amp; t) 'Référence client
              Sheets("COMPARATIF").Range("F" &amp; x) = Sheets("AFFRETEMENTS EN COURS").Range("I" &amp; t) 'Date de chargement
              Sheets("COMPARATIF").Range("G" &amp; x) = Sheets("AFFRETEMENTS EN COURS").Range("D" &amp; t) 'Ville de chargement
              Sheets("COMPARATIF").Range("K" &amp; x) = Sheets("AFFRETEMENTS EN COURS").Range("F" &amp; t) 'Ville d'arrivée
              Sheets("COMPARATIF").Range("M" &amp; x) = Sheets("AFFRETEMENTS EN COURS").Range("J" &amp; t) 'Date de livraison
              Sheets("COMPARATIF").Range("N" &amp; x) = Sheets("AFFRETEMENTS EN COURS").Range("K" &amp; t) 'Prix client
              Sheets("COMPARATIF").Range("O" &amp; x) = Sheets("AFFRETEMENTS EN COURS").Range("L" &amp; t) 'Prix affrété
              Sheets("COMPARATIF").Range("P" &amp; x) = Sheets("AFFRETEMENTS EN COURS").Range("M" &amp; t) 'Marge
              Sheets("COMPARATIF").Range("Q" &amp; x) = Sheets("AFFRETEMENTS EN COURS").Range("P" &amp; t) 'Affrété
              Sheets("COMPARATIF").Range("R" &amp; x) = "L'OT ne figure pas dans AKANEA"
              Sheets("COMPARATIF").Range("A" &amp; x &amp; ":Z" &amp; x).Interior.Color = RGB(255, 192, 0)
  
              Erreur_mois = True
  
  
          End If
  
      End If
  
  Next t
  
  
  '**********************If rows columns T and C are the same, then we will compare 2 other columns********************************************
  
  
  For n = 3 To DL_AFFRETEMENT
  
  For t = 2 To DL_COMPARATIF
  
  ' Si les OT sont les mêmes
  
  If CStr(Sheets("AFFRETEMENTS EN COURS").Range("T" &amp; n).Value) = CStr(Sheets("COMPARATIF").Range("C" &amp; t).Value) Then
  
  ' Alors on verifie que les prix correspondent et si pas correspondance on relève les colonnes + message et calcul de différence
  
      If CStr(Sheets("AFFRETEMENTS EN COURS").Range("K" &amp; n).Value) &lt;&gt; CStr(Sheets("COMPARATIF").Range("N" &amp; t).Value) Then
  
          x = x + 1
  
          For k = 1 To 17
  
              Sheets("COMPARATIF").Cells(x, k) = Sheets("COMPARATIF").Cells(t, k)
  
          Next
  
          Sheets("COMPARATIF").Range("R" &amp; x) = "Ecart de prix client"
  
          Sheets("COMPARATIF").Range("S" &amp; x) = Sheets("AFFRETEMENTS EN COURS").Range("K" &amp; n) - Sheets("COMPARATIF").Range("N" &amp; t)
  
          Sheets("COMPARATIF").Range("A" &amp; x &amp; ":S" &amp; x).Interior.Color = RGB(169, 208, 142)
  
          Erreur_mois = True
  
      End If
  
      If CStr(Sheets("AFFRETEMENTS EN COURS").Range("L" &amp; n).Value) &lt;&gt; CStr(Sheets("COMPARATIF").Range("O" &amp; t).Value) Then
  
          x = x + 1
  
          For k = 1 To 17
  
              Sheets("COMPARATIF").Cells(x, k) = Sheets("COMPARATIF").Cells(t, k)
  
          Next
  
          Sheets("COMPARATIF").Range("R" &amp; x) = "Ecart de prix affrété"
  
          Sheets("COMPARATIF").Range("S" &amp; x) = Sheets("AFFRETEMENTS EN COURS").Range("L" &amp; n) - Sheets("COMPARATIF").Range("O" &amp; t)
  
          Sheets("COMPARATIF").Range("A" &amp; x &amp; ":S" &amp; x).Interior.Color = RGB(47, 117, 181)
  
          Erreur_mois = True
  
      End If
  
  End If
  
  Next t
  
  Next n
  </code></pre>
  
  <p>Thank you in advance for your precious help.</p>