title: Excel VBA: For loop - Write value of row number
body: <p>The way my code currently works is like this:</p>
  
  <p>ws1 has multiple columns and rows of data. The first cell on every row in column A is the title. The titles are from a fixed selection (it's a drop down) which are determined by a list that is on ws2</p>
  
  <p>ws2 has the list of titles, which is h3 until LastRow</p>
  
  <p>ws3 I'm trying to set up a for loop so from A6 onwards it writes 1 row of data from ws1 of each title.</p>
  
  <p>So if ws2 has 5 titles, then ws3 will write 5 lines of data, and the data comes from the EntireRow on ws1</p>
  
  <p>Currently, my for loop will return the correct titles and the row numbers of the matched data (which I have verified using debug print)</p>
  
  <p>What I can't seem to figure out is how to get it to write on ws3 a line for each match. In my last line I have <code>ws3.Range("A6:A15").EntireRow.Value = ws1.Range("A" &amp; row_num2).EntireRow.Value</code> but this seems to print the last match on every row between A6 to A15 (so the same value on every row)</p>
  
  <p>I feel like it's something to do with the placement of the code, as it doesn't seem to loop correctly. It's looping until the last line and then only returning the last line.</p>
  
  <pre><code>Sub CardsCollection()
  
  Set ws1 = Sheets("Database")
  Set ws2 = Sheets("Insert")
  Set ws3 = Sheets("Sheet1")
  
  Dim myCell As Range
  Dim LastRow As Long
  
  LastRow = ws2.Cells(ws2.Rows.Count, "H").End(xlUp).Row
  Debug.Print LastRow
  
  Dim test_string As String
  test_string = "H" &amp; LastRow
  Dim test_range As Range
  Set test_range = ws2.Range(test_string)
  
  variable_condition = Range("C3")
  
  For Each myCell In ws2.Range("H3" &amp; ":" &amp; test_string)
      Debug.Print myCell
      row_num2 = Evaluate("MATCH(1,('" &amp; ws1.Name &amp; "'!A:A=""" &amp; myCell &amp; """)*('" &amp; ws1.Name &amp; "'!F:F=""" &amp; variable_condition &amp; """),0)")
      Debug.Print row_num2
      ws3.Range("A6:A15").EntireRow.Value = ws1.Range("A" &amp; row_num2).EntireRow.Value
  Next
  
  End Sub
  </code></pre>
  
  <p>Would appreciate any help! Thanks</p>