Vlookup with iferorr not working

Hello,

I created vlookup + iferror code but I am getting the error 1004 and I am stuck.

The principle is to do a vlookup in sheet("ana").Range("AG2:AG) (being Cells(i,30)) from other sheet Range(D:D )

Can you please help ?

Sub ana()
Dim lkupval As String
Dim lkparr As Range
Dim i As Long
Dim lkprez As Long
Set lkparr = Sheets("Business").Range("D1:D500000")
Sheets("2nd sheet").Activate
For i = 2 To Range("D2").End(xlDown).Row
Set lkpval = Cells(i, 1)
lkprez = Application.VLookup(lkpval, lkparr, 1, False)
If Not IsError(lkprez) Then
Cells(i, 30) = lkprez
Else
Cells(i, 30) = "#"
End If
Next i
End Sub

Thats because I want to adapt it to vlookup +iferror from book 1 to book2 and neither of this workbooks will have static name

Hi,

Still getting "Unable to get the vlookup property of the Worksheetfunction class" 1004 error highlighting

lkprez = Application.WorksheetFunction.VLookup(.Cells(i, 1).Value, lkparr, 1, False)

Quote from Fluff13

How about

Code

Sub ana()
   Dim lkupval As String
   Dim lkparr As Range
   Dim i As Long
   Dim lkprez As Variant
   Set lkparr = Sheets("Business").Range("D1:D" & Rows.Count).End(xlUp)
   With Sheets("2nd sheet")
      For i = 2 To .Range("D2").End(xlDown).Row
         ''/// searches in Sheets("Business")
         lkprez = Application.VLookup(.Cells(i, 1).Value, lkparr, 1, False)
         ''/// puts result in 2nd sheet
         If Not IsError(lkprez) Then
            .Cells(i, 30) = lkprez
         Else
            .Cells(i, 30) = "#"
         End If
      Next i
   End With
End Sub

Display More

It does work, however instead of counting 30 starting Col. D it counts starting col.A

Quote from Fluff13

In that case, how about

Code

lkprez = Application.VLookup(.Cells(i, 4).Value, lkparr, 1, False)

Tried it again and it retrieves "#"

Please find attached the zip archive.