title: Excel VBA - Compare two Columns in two different sheets then copy/paste - speed - It takes over an hour
body: <p>Here an absolute beginner at any form of coding, this is the first time ever I try to use VBA. </p>
  
  <p>I have managed after a week and a half of searching and testing and learning to reach the below posted code and I have hit a WALL (and I'm not even done yet!) </p>
  
  <p>What I am trying to achieve:</p>
  
  <p>Compare the data in sheet1 with the data in sheet2 found in Columns K respectively A (there are ca. 55.000 rows in K and 2500 in A) the data might repeat itself as these are product codes and it's ok as at the end of this I want to be able to see which ones have expired. </p>
  
  <p>so .. If K = A then it has to copy adjacent values found in Sheet2 - columns O, P &amp; Q and Paste them in Sheet2 - Columns O, P &amp; Q and if no match is found then right not found. In the Example below I have only tried to copy Q, it would probably take forever if I tried adding O &amp; P.</p>
  
  <p>(Note: I have found this code in one of the forms here and used it after trying different other ways with select/ Copy/ Paste etc. but none have worked)</p>
  
  <p>Later I would like to try adding another column in Sheet1 and based on the Date which will be copied to Sheet1 and into column P populate it with Expired or Soon to be expired depending on the case, but this is an entire different story and I haven't even begun thinking how to do it. </p>
  
  <p>The problem is that my current code takes over an hour and it's still not finished yet while I am writing this!!! And I do not understand where have I gone wrong ....   </p>
  
  <pre><code>Dim lastRow1 As Long
  Dim lastRow2 As Long
  Dim tempVal As String
  
  
  
  lastRow1 = Sheets("Sheet1").Range("K" &amp; Rows.Count).End(xlUp).Row
  lastRow2 = Sheets("Sheet2").Range("A" &amp; Rows.Count).Row
  
  For sRow = 2 To lastRow1
          tempVal = Sheets("MatCode").Cells(sRow, "A").Text
  For tRow = 2 To lastRow2
              If Sheets("Sheet1").Cells(tRow, "K") = tempVal Then
              Sheets("Sheet1").Cells(tRow, "Q") = Sheets("Sheet2").Cells(sRow, "Q")
              End If
  
      Next tRow
      Next sRow
  
  Dim match As Boolean
  'now if no match was found, then put NO MATCH in cell
      For lRow = 2 To lastRow2
          match = False
          tempVal = Sheets("Sheet1").Cells(lRow, "K").Text
  
  For sRow = 2 To lastRow1
              If Sheets("Sheet2").Cells(sRow, "A") = tempVal Then
                  match = True
              End If
          Next sRow
  If match = False Then
              Sheets("Sheet1").Cells(lRow, "Q") = "NO MATCH"
          End If
      Next lRow
  End Sub
  </code></pre>
  
  <p>I have also used: </p>
  
  <pre><code>With Application
      .AskToUpdateLinks = False
      .ScreenUpdating = False
      .DisplayAlerts = False
      .EnableEvents = False
  End With
  </code></pre>
  
  <p>To make sure nothing stands in the way. </p>
  
  <p>Please Help! </p>