excel - VBA vlookup reference in different sheet

Question

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?

Answer 1

try this:

Dim ws as Worksheet

Set ws = Thisworkbook.Sheets("Sheet2")

With ws
    .Range("E2").Formula = "=VLOOKUP(D2,Sheet1!$A:$C,1,0)"
End With

End Sub

This just the simplified version of what you want.
No need to use Application if you will just output the answer in the Range("E2").

If you want to stick with your logic, declare the variables.
See below for example.

Sub Test()

Dim rng As Range
Dim ws1, ws2 As Worksheet
Dim MyStringVar1 As String

Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")
Set rng = ws2.Range("D2")

With ws2
    On Error Resume Next 'add this because if value is not found, vlookup fails, you get 1004
    MyStringVar1 = Application.WorksheetFunction.VLookup(rng, ws1.Range("A1:C65536").Value, 1, False)
    On Error GoTo 0
    If MyStringVar1 = "" Then MsgBox "Item not found" Else MsgBox MyStringVar1
End With

End Sub

Hope this get's you started.