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
Range("E2") = Application.WorksheetFunction.VLookup(Range("D2"), _
Worksheets("Sheet1").Range("A1:C65536"), 1, False)
Problem, it returns error code 1004. I read that it was because I needed to Select Sheet 1 before running the formulas such as:
ThisWorkbook.Worksheets("Sheet1").Select
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.
What is the correct way to refer to a different sheet in this case?