Run Time err 1004: Unable to get the vlookup property of the worksheet function class

[kavkazi]

Hello,

I am trying to do a vlookup on current worksheet compared to a range in another file.

I get a "Run Time Error 1004: Unable to get the vlookup property of the worksheet function class".

After some investigation, I found out that if there are some values that dont match up this could be a problem. In my case I will run into that situiation. But for testing purposes, I tried manipulating the data so that this doesnt happen. With no luck.

Here is my code:




Dim lastrow as Long

lastrow = Sheets("Master List").Range("A1").End(xlDown).Row

For i = 2 To lastrow
Sheets("Master List").Cells(i, "B").Value = Application.WorksheetFunction.VLookup(Sheets("Master List").Cells(i, "A"), "'T:\Operations+Technology\NOC\Abuse Emails\[Customer Data for Abuse Emails.xls]Report'!$E$4:$G$60000", 2, False)
Next i



Thanks in Advance

[Bob Phillips]

Maybe some values don't match. Try



Const LOOKUP_TABLE AS string = _
"'T:\Operations+Technology\NOC\Abuse Emails\[Customer Data for Abuse Emails.xls]Report'!$E$4:$G$60000"
Dim lastrow as Long

With Sheets("Master List")

lastrow = .Range("A1").End(xlDown).Row

For i = 2 To lastrow

If Not IsError(Application.VLookup(.Cells(i, "B").Value, , LOOKUP_TABLE, 2, False)) Then

.Cells(i, "B").Value = Application.VLookup(.Cells(i, "A"), LOOKUP_TABLE, 2, False)
Else

.Cells(i, "B").Value = ""
End If
Next i
End With

[Bob Phillips]

That isn't going to work, you would need to evaluate the formula, and that always errors in my tests.