title: VBA: Use "Find" to get the row of a value / fixing object variable errors
body: <p>I need to use the "find" function to get the row of a value, where that value is defined from another worksheet, but I keep getting object-variable not defined errors even when I try defining everything.  </p>
  
  <p>I was initially able to get the code below to work. (I paste the "test" version of the code that only uses a small range of 20 cells. The result is placed in cell "A1" to make sure it works correctly in testing.)</p>
  
  <pre><code>X = Range("D6").Value
  
  Range("A1").Value = Range("D2:D20").Find(X).Row
  </code></pre>
  
  <p>But when I use this line in a large data set, I keep getting error 91 ("Object variable or With block variable not set").</p>
  
  <p>So instead, I try code that defines everything more explicitly, but for the life of me, I can't get this code to work even on the small test set. (I also include a few lines that I've tried but that also produce errors.)</p>
  
  <pre><code> Sub Test3()
  
      Sheets("2017").Select
      Dim X As Long
      X = Range("D6").Value
  
      Dim ws As Worksheet
      Set ws = Worksheets("2017")
  
      Dim SearchRange As Range
      Dim FindRow As Range
      Set SearchRange = ws.Range("D2", ws.Range("D20").End(xlUp))
      Set FindRow = SearchRange.Find(X, LookIn:=xlValues, lookat:=xlWhole)
  
      If Not FindRow Is Nothing Then ReturnRowNumber = FindRow.Row
  
      'Range("A1").Value = FindRow 'Running this row gives application-defined error 1004
  
      Dim i_2017 As Long
      i_2017 = FindRow.Rows(1)  'All of these rows give object variable not set error 91
      'i_2017 = FindRow
      'i_2017 = FindRow.Rows.Item(1)
      Range("A1").Value = i_2017 'Check that the sub returns 6
  
  End Sub
  </code></pre>
  
  <p>If I try to return "FindRow" in cell "A1", I get error 1004 ("Application-defined or object-defined error")</p>
  
  <p>If I try to store "FindRow" as another variable or use the "Item" property, I get error 91 ("Object variable or With block variable not set")</p>