CODEWITHSUNDEEP
vbaexcelvlookup
NetAdmin
NetAdmin

Reputation: 77

Vlookup crashes program

I have a program that seems to crash as soon as it can't find an entry in a VLookup.

To try and comply with some NDAs any identifying information will be removed.

LcTg = Cells(i, "K")
Bldg = Mid(LcTg, 1, 1)
Zone = Mid(LcTg, 2, 2)
Aisle = Mid(LcTg, 4, 2)
If Not (IsNumeric(Zone)) Then
    Aisle = Application.VLookup(Zone, Worksheets("ST" & Bldg & " Zones").Range("A2:B100"), 2, False)
    If IsError(Aisle) Then
        Aisle = "N/A"
    End If
End If
Cells(i, nextColumn) = Aisle

This bit of code is designed to figure out if the Zone is already numerically identified which in all cases means the Aisle is correct already, and if it isn't, it searches a small table to find the equivalent Aisle, for the Alphanumeric zone, e.g. A1.

This code iterates 100,000s of times if not millions. The code abruptly stops when it hits something that isn't in the VLookup.

I am not a professional coder, so feel free to explain things as simply as possible.

Edit

I added minor error handling and it works fine. I apologize for it being such a silly issue.

If Not (IsNumeric(Zone)) Then
        On Error Resume Next 'if error, the code will go on anyway
        Aisle = Application.VLookup(Zone, Worksheets("ST" & Bldg & " Zones").Range("A2:B100"), 2, False)
        If Err.Number <> 0 Then
            Aisle = "N/A"
        End If
        On Error GoTo 0 'no error, coming back to default conditions
End If

Upvotes: 1

Views: 101

Answers (2)

user4039065
user4039065

Reputation:

You can only catch a no-match error from Application.VLookup into a variant. 

dim Aisle as variant
...
Aisle = Application.VLookup(Zone, Worksheets("ST" & Bldg & " Zones").Range("A2:B100"), 2, False)
If IsError(Aisle) Then
    Aisle = "N/A"
End If

Upvotes: 1

Alex de Jong
Alex de Jong

Reputation: 1267

What I normally do in this situation is that I first check if the Variable exists by using If application.worksheetfunction.countif([Range],[Value]) > 0 then to avoid any errors.

Upvotes: 1

Related Questions