title: VBA vlookup reference in different sheet
body: <p>In Excel 2007, I am looping through the values of column 4 in Sheet 2. Still in Sheet 2, I want to output the result of my vlookup formula into column 5. The vlookup formula needs to refer to Sheet 1 where the reference columns are. In order to do so I have the following formula</p>
  
  <pre><code>Range("E2") = Application.WorksheetFunction.VLookup(Range("D2"), _
            Worksheets("Sheet1").Range("A1:C65536"), 1, False)
  </code></pre>
  
  <p>Problem, it returns error code 1004. I read that it was because I needed to Select Sheet 1 before running the formulas such as:</p>
  
  <pre><code>ThisWorkbook.Worksheets("Sheet1").Select
  </code></pre>
  
  <p>But then the searched value Range ("D2") doesn't belong to Sheet 1 and it still return code 1004 after having brought Sheet 1 into view.</p>
  
  <p>What is the correct way to refer to a different sheet in this case?</p>