Need to skip VLoopkup (no Value) to next step in For Loop

[Totti_K]

I am writing a simple code to loop through a row of data. I have a for loop that goes from one cell to anohter. When vlookup reach a cell where there is not value, the macro stops there. How can I skip to the next vLoopUp action? If iRow 3 has an error, skip to iRow 4.

For iRow = 2 To 5
sLookUpValue = Worksheets("Item").Range(sDeptColumnLetter & iRow).Value
sResult = Application.VLookup((sLookUpValue), Worksheets("Department").Range("A1:D200"), 4, False)
Worksheets("Item").Range(sDeptColumnLetter & iRow) = sResult
Next iRow

 

[Yongle]

Try this

For iRow = 2 To 5
   sLookUpValue = Worksheets("Item").Range(sDeptColumnLetter & iRow).Value
   On Error Resume Next
     sResult = Application.VLookup((sLookUpValue), Worksheets("Department").Range("A1:D200"), 4, False)
     If Err.Number = False then  Worksheets("Item").Range(sDeptColumnLetter & iRow) = sResult
  On Error GoTo 0
Next iRow

 

[Totti_K]

Try this

For iRow = 2 To 5
   sLookUpValue = Worksheets("Item").Range(sDeptColumnLetter & iRow).Value
   On Error Resume Next
     sResult = Application.VLookup((sLookUpValue), Worksheets("Department").Range("A1:D200"), 4, False)
     If Err.Number = False then  Worksheets("Item").Range(sDeptColumnLetter & iRow) = sResult
  On Error GoTo 0
Next iRow

Thanks. this works perfectly. I just need to add the 'end if'? also the 'On Error Goto 0 is closing of the error catch?

 

[Yongle]

I just need to add the 'end if'?

No
- everything is in ONE line
- the code will fail if you add End If

End If is required if written like this

For iRow = 2 To 5
   sLookUpValue = Worksheets("Item").Range(sDeptColumnLetter & iRow).Value
   On Error Resume Next
     sResult = Application.VLookup((sLookUpValue), Worksheets("Department").Range("A1:D200"), 4, False)
     If Err.Number = False Then  
        Worksheets("Item").Range(sDeptColumnLetter & iRow) = sResult
     End IF
  On Error GoTo 0
Next iRow

the 'On Error Goto 0 is closing of the error catch?

On Error Resume Next tells VBA to carry on and run the next line
On Error Goto 0 resets handling, making VBA will stop at the next error

VLookup

 

[Totti_K]

Thanks @Yongle . Its working perfectly!