title: vba find value then paste another into different cell in another column
body: <p>I'm currently running a Macro that looks for a value from column A of 'sheet 1' in column C of sheet 2, if these match then the value from column B of sheet 1 should be copied to column M of the corresponding row in sheet 2. </p>
  
  <p>The Macro I have works, but because this is a massive worksheet, the loop in it is taking far too much time. This is because sheet 1 has around 300,000 rows and the value in each instance is unique. in Sheet 2 there are around 50,000 rows. It's been running overnight and has only reached 60,000 rows in sheet 1 so far</p>
  
  <p>I'm by no means a VBA expert, or even intermediate but from what I've read maybe using Find would be faster than looking for a match and looping?</p>
  
  <p>this is the macro i'm currently using</p>
  
  <pre><code> Option Explicit
  
  Sub lookupandcopy()
  Application.Screenupdating = True
  
  Dim j As Long, i As Long, lastRow1 As Long, lastRow2 As Long
  Dim sh_1, sh_3 As Worksheet
  Dim MyName As String
  
  Set sh_1 = Sheets("sheet1") 
  Set sh_3 = Sheets("sheet2")
  
  
  lastRow1 = sh_1.UsedRange.Rows.Count 
  
  For j = 2 To lastRow1
  MyName = sh_1.Cells(j, 1).Value 
  
  
  lastRow2 = sh_3.UsedRange.Rows.Count
  
  For i = 2 To lastRow2
      If sh_3.Cells(i, 3).Value = MyName Then
          sh_3.Cells(i, 13).Value = sh_1.Cells(j, 2).Value 
      End If
  
      Next i
  
    Next j
  
  Application.Screenupdating = True
  End Sub
  </code></pre>
  
  <p>If I've missed anything off or any other detail that's needed please let me know!</p>