title: Excel VBA find row number based on criteria in two columns, no loop
body: <p>A little background, my sheet "Data" consists of a table, which my macro is supposed to populate. The table has dates running down the first column (Column P), and a few names as headers. My current macro, as seen below, loops through all my sheets, except the ones specified not to loop through, then in each sheet it loops through each cell in the range W7:W200. It then looks to match the right 10 values in the cell with a date in the column P on sheet "Data" (and sets that row as HdrRow). At the same time, it looks for the value in A9 in whatever sheet it is looping through, in order to match that value to a column header in sheet "Data" (and sets that column as HdrCol). After finding the row and column (intersecting cell), the macro then pastes the values of the cell it is looping through into that intersecting cell.</p>
  
  <p>I am having trouble with this next part, I am looking to add another criteria for finding a row. I would like the macro to not only find a matching date in column P, but also a value in column Q that matches with the value in A1 of whichever sheet it is looping through; and then set that row as HdrRow. If possible, id like to not use a loop for this.</p>
  
  <pre><code>Sub Values()
      Dim HdrCol As Range
      Dim Site As String
      Dim SearchRange As Range
      Dim HdrRow As Range
      Dim FinDate As Date
      Dim ws As Worksheet
      Dim rng As Range
  
  
  
  
      ' Fill in Actual Value
      Sheets("Data").Range("W2:W100000").ClearContents
  
      For Each ws In ActiveWorkbook.Worksheets
          'Dont Copy Data from these worksheets
          If ws.Name &lt;&gt; "Portfolio" And ws.Name &lt;&gt; "Master" And ws.Name &lt;&gt; "Template" _
              And ws.Name &lt;&gt; "Coal" And ws.Name &lt;&gt; "E&amp;P" And ws.Name &lt;&gt; "Gen" _
              And ws.Name &lt;&gt; "Hydro" And ws.Name &lt;&gt; "LNG" And ws.Name &lt;&gt; "Midstream" _
              And ws.Name &lt;&gt; "Solar" And ws.Name &lt;&gt; "Transmission" _
              And ws.Name &lt;&gt; "Wind" And ws.Name &lt;&gt; "Data" Then
  
              For Each cell In ws.Range("W7:W200")
  
                  If cell &lt;&gt; "          " Then
  
                      Site = ws.Range("A9").Value
                      FinDate = Right(cell, 10)
  
                      'Find column ref
                      Set HdrCol = Sheets("Data").Range("P1:W1").find(Site, lookat:=xlPart)
                      If Not HdrCol Is Nothing Then
                      End If
  
                      'Find row ref
                      Set SearchRange = Sheets("Data").Range("P1", Range("P100000").End(xlUp))
                      Set HdrRow = SearchRange.find(FinDate, LookIn:=xlValues, lookat:=xlWhole)
  
  
                      Application.Goto Reference:=Cells(HdrRow.Row, HdrCol.Column)
  
                      If IsEmpty(Sheets("Data").Cells(HdrRow.Row, HdrCol.Column)) Then
                          cell.Copy Sheets("Data").Cells(HdrRow.Row, HdrCol.Column)
                      Else
                          cell.Copy Sheets("Data").Cells(HdrRow.Row, HdrCol.Column).End(xlDown).Offset(1, 0)
                      End If
                  End If
              Next
          End If
      Next
  End Sub
  </code></pre>