CODEWITHSUNDEEP
excelvbavlookup
Skudra21
Skudra21

Reputation: 3

Vlookup + Iferror in VBA error "1004"

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

Upvotes: 0

Views: 324

Answers (2)

David Zemens
David Zemens

Reputation: 53623

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.).

Upvotes: 3

Ali M Irshad
Ali M Irshad

Reputation: 116

check the range in loop.. it must be string. u r using "i" as string it should be there as variable.

Upvotes: 0

Related Questions