title: Excel VBA - .Find method between workbooks
body: <p><strong>Question:</strong><br>
  Why is the <code>Range.Find</code> method not working when referencing a different workbook?</p>
  
  <p><strong>Problem:</strong>
  I'm attempting to copy data between workbooks, but the <code>Range.Find</code> method is stopping with a "Run-time Error 1004". I'm using Excel 2007 on a Windows 7 machine.</p>
  
  <p><strong>Details:</strong>
  On two workbooks, only Sheet1 is referenced or used for each workbook. I have a procedure (ztest) with the following outline:</p>
  
  <ol>
  <li>Format the sheet</li>
  <li>Loop through all cells in column E of workbook #1</li>
  <li>Using the <code>Range.Find</code> method, find the value in column E of the workbook #2</li>
  <li>Once found, set workbook #1 offset column = workbook #2 offset column</li>
  </ol>
  
  <p>I'd like to do this with <code>.Find</code> - <em>not</em> using HLOOKUP or the like.</p>
  
  <p>I've simplified the code somewhat, to narrow down what exactly is going on. This doesn't show step 4 above, but the error occurs in step 3, in the statement containing the <code>.Find</code> method:</p>
  
  <pre><code>Public Sub ztest2()
  'set workbook titles
  Const w1 As String = "05AR 20130920.xlsx"
  Const w2 As String = "05AR 20130923.xlsx"
  Dim cl As Variant
  
  With Workbooks(w2).Worksheets(1)
    'format the sheet
    .Range("A1", "D1").EntireColumn.Hidden = True
    'loop through all cells column E of workbook #1
    For Each cl In .Range("E2", Cells(Rows.Count, "E").End(xlUp))
      'find value of current cell in column E, workbook #2
      Workbooks(w1).Worksheets(1) _
      .Range("E2", Cells(Rows.Count, "E").End(xlUp)) _
      .Find(what:=cl.Value, LookIn:=xlValues).Select
    Next
  End With
  
  End Sub
  </code></pre>