Vlookup crashes program

Question

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

Have a look at [VBA error handling](https://stackoverflow.com/questions/1038006/good-patterns-for-vba-error-handling). — jsheeran, Mar 09 '18 at 12:52
You need `Option Explicit` at the top of the page and `dim aisle as variant` in your code. — , Mar 09 '18 at 13:11

score 1 · Answer 1 · answered Mar 09 '18 at 12:53

1

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.

answered Mar 09 '18 at 12:53

Alex de Jong

1,257
1
11
23

While that's a good, non-destructive test for smaller loads, that might not be the best approach for *'code iterates 100,000s of times if not millions'*. – Mar 09 '18 at 13:18
Unfortunately, that line also causes the program to skip out of the subroutine. – NickPomroy Mar 09 '18 at 13:45

score 1 · Answer 2 · answered Mar 09 '18 at 13:16

1

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

answered Mar 09 '18 at 13:16

I apologize, as Aisle was declared outside of the code snippet I copied. Aisle is set as Variant, the IsError(aisle) catch causes the same crash. – NickPomroy Mar 09 '18 at 13:57

Vlookup crashes program

Edit

2 Answers2