For loop: skip value when error occurs

Question

I have a table on Sheet3 and a reference list on Sheet2 which refers to the table data on Sheet3, references on Sheet2 are in a different order to the references in the table on Sheet3. I am currently using the VLOOKUP command to search for the corresponding reference on Sheet3 and populate the column on Sheet2, the list is long therefore I used a for loop which goes through all the rows and applies the VLOOKUP command as shown: Dim Y As Integer

Y = 2
For Y = 1 To 108
Worksheets(2).Range("C" & Y).Value = Application.WorksheetFunction.VLookup(ActiveWorkbook.Sheets(2).Range("A" & Y), ActiveWorkbook.Sheets(3).Range("A2:B113"), 2, False)
Next Y

Note: I understand having everything on 1 line is messy and I should really be using variables but this way it is easier for me to read the statements as I have lots of them which are similar on 1 page.

Now my problem is that some of the reference numbers shown on Sheet1 are missing on Sheet2 and for these reference numbers I get an error and debug popup for my VLOOKUP statement therefore, is there any way that I could go through the loop as normal but once encountering these missing values just skip them and go onto the next ones? I mean something like if an error occurs skip Y?

Dmitry Pavliv Dmitry Pavliv · Accepted Answer · 2014-02-21T09:32:38

Is there any way that I could go through the loop as normal but once encountering these missing values just skip them and go onto the next ones?

Yes it is. Use IsError function:

Dim res
Y = 2
For Y = 1 To 108
    res = Application.VLookup(ActiveWorkbook.Sheets(2).Range("A" & Y), ActiveWorkbook.Sheets(3).Range("A2:B113"), 2, False)
    If Not IsError(res) Then
        Worksheets(2).Range("C" & Y).Value = res
    End If
Next Y

Another approach (without loop - more efficient way) would be to use something like this:

With ActiveWorkbook.Worksheets(2).Range("C1:C108")
    .Formula = "=IFERROR(VLOOKUP(A1,'" & ActiveWorkbook.Worksheets(3).Name & "'!$A$2:$B$113,2,0),"""")"
    .Value = .Value
End With

in this approach if VLOOKUP returns #N/A, cell would be empty. Part .Value = .Value rewrtites formulas in cells with their result values.

For loop: skip value when error occurs

1 Answers