title: Find Function in Loop VBA
body: <p>I have the following VBA code. I want to make a calculation, but this can only be done if the IF statements are met. This means that the dates in 5 different sheets should be the same in a certain cell and that client names should be equal in another. The dates and names are displayed in different ways along the sheets.
  If I would use the first code, then the code works. BUT the first two rows used in the if statement code, finds not the correct value, because the fomrat/display of these are not the same in the different sheets that are used.
  So I think it is necessairy to use the find function, to find the correct value needed for the calculation. The code using the find function is shown in the second macro provided.When I try to run this code, I get an Error 91. So assume something is wrong with the find functions I made, but I can not find the mistake I made. 
  Does somebody know what is going wrong in the second code?</p>
  
  <pre><code>Option Explicit
  
  Sub DoSomething1()
  
  
      'Declare Variables
      Dim i As Long, r As Long, c As Long
      Dim count as Double, lastColData as Double, LastRowData as Double, lastRowInput as Double, StartSearch2 As Double
      Dim shtData As Worksheet, shtInput As Worksheet, shtInputI As Worksheet, shtInputII As Worksheet, shtInputIII As Worksheet, shtInputIV As Worksheet, shtInputV As Worksheet, shtInputVI As Worksheet, shtCopy As Worksheet
      Set shtData = Sheets("Data")
      Set shtInput = Sheets("INPUT")
      Set shtInputI = Sheets("INPUTP")
      Set shtInputII = Sheets("INPUTR")
      Set shtInputIII = Sheets("INPUTF")
      Set shtInputIV = Sheets("INPUTQ")
      Set shtInputV = Sheets("INPUTPF")
      LastColData = shtData.Cells(4, shtData.Columns.count).End(xlToLeft).Column
      LastRowData = shtData.Cells(shtData.Rows.count, "A").End(xlUp).row
      LastRowInput = shtInput.Cells(shtInput.Rows.count, "A").End(xlUp).row
  
  
      'Search starting from which row?
      StartSearch2 = InputBox("Vanaf welke rij wil je zoeken?", "Start", Default:=6)
  
  
      'Loop Code
      For r = StartSearch2 To LastRowData
          For c = 2 To LastColData
              count = 0
              For i = 2 To LastRowInput
  
  
                  If shtInput.Cells(i,10).Value = shtData.Cells(r,1).Value And _
              shtInputI.Cells(r, 1).Value = shtData.Cells(r, 1).Value And _
                      shtInputIV.Cells(r, 1).Value = shtData.Cells(r, 1).Value And _
                      shtInputIII.Cells(r, 1).Value = shtData.Cells(r, 1).Value And _
                      shtInputII.Cells(r, 1).Value = shtData.Cells(r, 1).Value Then   'Dates are equal in different sheets
  
                              If shtInput.Cells(i,32).Value = shtData.Cells(4,c).Value And _
                  shtInputI.Cells(4, c).Value = shtData.Cells(4, c).Value And _
                                  shtInputV.Cells(4, c).Value = shtData.Cells(4, c).Value And _
                                  shtInputIV.Cells(4, c).Value = shtData.Cells(4, c).Value And _
                                  shtInputIII.Cells(4, c).Value = shtData.Cells(4, c).Value And _
                                  shtInputII.Cells(4, c).Value = shtData.Cells(4, c).Value Then   'Names are equal in different sheets
  
  
                              count = count + (shtInputI.Cells(r, c).Value + shtInputII.Cells(r, c)) * shtInputV.Cells(5, c).Value * shtInputIV.Cells(r, c).Value * shtInputIII.Cells(r, c).Value
  
                              End If
                  End If
  
  
  
              Next i
  
              shtData.Cells(r, c).Value = count
          Next c
      Next r
  
  End Sub
  </code></pre>
  
  <hr>
  
  <pre><code>Option Explicit
  
  Sub DoSomething2()
  
  
      'Declare Variables
      Dim i As Long, r As Long, c As Long
      Dim count as Double, lastColData as Double, LastRowData as Double, lastRowInput as Double, StartSearch2 As Double
      Dim shtData As Worksheet, shtInput As Worksheet, shtInputI As Worksheet, shtInputII As Worksheet, shtInputIII As Worksheet, shtInputIV As Worksheet, shtInputV As Worksheet, shtInputVI As Worksheet, shtCopy As Worksheet
      Set shtData = Sheets("Data")
      Set shtInput = Sheets("INPUT")
      Set shtInputI = Sheets("INPUTP")
      Set shtInputII = Sheets("INPUTR")
      Set shtInputIII = Sheets("INPUTF")
      Set shtInputIV = Sheets("INPUTQ")
      Set shtInputV = Sheets("INPUTPF")
      LastColData = shtData.Cells(4, shtData.Columns.count).End(xlToLeft).Column
      LastRowData = shtData.Cells(shtData.Rows.count, "A").End(xlUp).row
      LastRowInput = shtInput.Cells(shtInput.Rows.count, "A").End(xlUp).row
  
  
      'Search starting from which row?
      StartSearch2 = InputBox("Vanaf welke rij wil je zoeken?", "Start", Default:=6)
  
  
      'Loop Code
      For r = StartSearch2 To LastRowData
          For c = 2 To LastColData
              count = 0
              For i = 2 To LastRowInput
  
  
  
          If shtInput.Cells.Find(What:=shtInput.Cells(i, 10).Value, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext) = shtData.Cells(r, 1).Value And _
              shtInputI.Cells.Find(What:=shtInputI.Cells(r, 1).Value, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext) = shtData.Cells(r, 1).Value And _
                      shtInputIV.Cells(r, 1).Value = shtData.Cells(r, 1).Value And _
                      shtInputIII.Cells(r, 1).Value = shtData.Cells(r, 1).Value And _
                      shtInputII.Cells(r, 1).Value = shtData.Cells(r, 1).Value Then
  
                              If shtInput.Cells.Find(What:=shtInput.Cells(i, 32).Value, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext) = shtData.Cells(4, c).Value And _
                              shtInputI.Cells.Find(What:=shtInputI.Cells(4, c).Value, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext) = shtData.Cells(4, c).Value And ?
                                  shtInputV.Cells(4, c).Value = shtData.Cells(4, c).Value And _
                                  shtInputIV.Cells(4, c).Value = shtData.Cells(4, c).Value And _
                                  shtInputIII.Cells(4, c).Value = shtData.Cells(4, c).Value And _
                                  shtInputII.Cells(4, c).Value = shtData.Cells(4, c).Value Then
  
                              count = count + (shtInputI.Cells(r, c).Value + shtInputII.Cells(r, c)) * shtInputV.Cells(5, c).Value * shtInputIV.Cells(r, c).Value * shtInputIII.Cells(r, c).Value
  
                              End If
                  End If
  
  
  
              Next i
  
              shtData.Cells(r, c).Value = count
          Next c
      Next r
  
  End Sub
  </code></pre>