title: Fastest way to find a value from another data set (VBA) (code speedup)
body: <p>I have two datasets that are both within a worksheet, call them Data and IBES. The code checks whether the 6 variables are the same in each dataset and then writes the value from a specific column to the other dataset. To find this value the code runs through 288503 lines which is dramatically slow. </p>
  
  <p>My question is, how to speedup this code? </p>
  
  <p>Thank you very much!</p>
  
  <pre><code>Public Function GetRightValue()
  
  Dim i As Integer
  Dim j As Long
  
  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False
  Application.DisplayStatusBar = False
  Application.EnableEvents = False
  ActiveSheet.DisplayPageBreaks = False
  
  
  For i = 2 To 1511           'Loop over all values from total dataset
          For j = 2 To 288503      'Loop over all values from IBES file
              If Worksheets("Data").Cells(i, 3) = Worksheets("IBES").Cells(j, 1) Then
                  If Worksheets("Data").Cells(i, 7) = Worksheets("IBES").Cells(j, 6) Then
                      If Worksheets("Data").Cells(i, 10) = Worksheets("IBES").Cells(j, 9) Then
                          If Worksheets("Data").Cells(i, 13) = Worksheets("IBES").Cells(j, 11) Then
                              If Worksheets("Data").Cells(i, 8) = Worksheets("IBES").Cells(j, 7) Then
                                  If Worksheets("Data").Cells(i, 14).Text = Worksheets("IBES").Cells(j, 13).Text Then
                                      Worksheets("Data").Cells(i, 12) = Worksheets("IBES").Cells(j, 10).Text
                                      Worksheets("Data").Cells(i, 18) = Worksheets("IBES").Cells(j, 16).Text
                                  End If
                              End If
                          End If
                      End If
                  End If
              End If
          Next j
  Next i
  
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
  Application.DisplayStatusBar = True
  Application.EnableEvents = True
  ActiveSheet.DisplayPageBreaks = True
  
  End Function
  </code></pre>