title: Excel VBA :: Find function in loop
body: <p>I'm trying to loop through several worksheets that contain some source data that has to be copied to one main sheet, called "PriorityList" here.
  First of all, the sub is not working and I think the error is somewhere in the "<strong>find</strong>"-method. Second, the sub takes quite long to run, and I think this is maybe because the "find"-method searches through the whole sheet instead of only the relevant range?</p>
  
  <p>Thank you very much for your answers!</p>
  
  <p>Patrick</p>
  
  <pre><code>Sub PriorityCheck()
  'Sub module to actualise the PriorityList
  
  Dim CurrWS As Long, StartWS As Long, EndWS As Long, ScheduleWS As Long
  StartWS = Sheets("H_HS").Index
  EndWS = Sheets("E_2").Index
  
  Dim SourceCell As Range, Destcell As Range
  
  For CurrWS = StartWS To EndWS
  
      For Each SourceCell In Worksheets(CurrWS).Range("G4:G73")
  
          On Error Resume Next
  
          'Use of the find method
          Set Destcell = Worksheets(CurrWS).Cells.Find(What:=SourceCell.Value, After:=Worksheets("PriorityList").Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
  
          'Copying relevant data from source sheet to main sheet
          If Destcell &lt;&gt; Nothing Then
              Destcell.Offset(0, 2).Value = SourceCell.Offset(0, 5).Value + Destcell.Offset(0, 2).Value
              If SourceCell.Offset(0, 3).Value = "x" Then Destcell.Offset(0, 3).Value = "x"
              End If
          End If
  
          On Error GoTo 0
  
      Next SourceCell
  
  Next CurrWS
  
  End Sub
  </code></pre>