Vlookup + Iferror in VBA error “1004”

Question

I have not been able to figure out what is wrong with my script as it seems to be not difficult at all, so I hope that I will find the answer here.

I have two sheets (Sheet1 and Sheet2) where in both are lists that change over time (info from sheet1 gets pased in sheet2 every time). I want to to run vlookup function for every row in Sheet1 and return "new" (in 28th column) if the entry does not exist in sheet2.

Something is wrong with the code as I get 1004 error all the time regarding iferror&vlookup function.

Thanks in advance,

Sub vlookup_iferror ()
 Dim i As Integer
 Dim finalrow As Integer

 finalrow = Sheets("Sheet1").Range("A100000").End(xlUp).Row

  For i = 2 To finalrow
  Cells(i, 28) = Application.IfError(Application.VLookup(Sheets("Sheet1").Range("i, 2"), Sheets("Sheet2").Range("B:B"), 1, False), "New")
  Next i
End sub

Proper syntax is Application.WorksheetFunction.IFERROR() etc. Not sure why you want to do it this way, instead of using .Range().Find(), as it takes longer.. Also, if youre trying to dynamically find the last row, something like Sheets(1).Cells(Sheets(1).Rows.Count, 1).End(xlUp).Row might work better. — Tyeler

David Zemens David Zemens · Accepted Answer · 2016-10-11T20:34:01

You're getting a 1004 because Sheets("Sheet1").Range("i, 2") is invalid range assignment.

To fix it:

Range("i, 2") should be Cells(i, 2)

Range takes an address argument (e.g., Range("A1") or Range("B" & i), etc.), Cells takes row/column index arguments (e.g., Cells(1,2), etc.).

Vlookup + Iferror in VBA error “1004”

2 Answers