Excel VBA - Userform vlookup error handling

Question

I have a userform with a combobox populated with product names from my settings sheet, and I am trying to display a label with the product description once the user has selected a product from the combobox.

This works rather well, unless the user selects a product for which there is no product description, hence the vlookup would return empty and cause an error with the userform.

I tried several If Error - if is empty - Description is 0 - Description is empty statements , but none seem to work, including OnError GoTo Errorhandler. What am I doing wrong here?

    Private Sub Problem_List_Change()

Description = Application.WorksheetFunction.VLookup(Problem_List.Text, Worksheets("Settings").Range("l3:o1000"), 4, False)

    If IsError(Description) Then
        Desc.Caption = ""
    Else
        Desc.Caption = Description
    End If

End Sub

thanks, a2k

@Nathan_Sav tried but still error ` If Description Is Nothing Then` — Armitage2k
– Armitage2k, Commented Sep 27, 2016 at 8:26

Nathan_Sav · Accepted Answer · 2016-09-27 08:25:50Z

1

This works for me.

Sub x()

Dim r As Variant

On Error Resume Next
r = Application.WorksheetFunction.VLookup(5, Range("a1:b4"), 2, False)

If Not IsEmpty(r) Then
    Debug.Print "Found"
Else
    Debug.Print "Not Found"
End If

End Sub

answered Sep 27, 2016 at 8:25

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nathan_Sav Over a year ago

No worries, on a side note, if you are to retrieve multiple values, it may be an idea to use a combination of MATCH to find your row number and then reference the sheet off the row number. 1 return should be fine.

Armitage2k Over a year ago

I am doing exactly that for the rest of the code, but just for this particular userform it would be overkill since I only have one value to lookup. But glad to know i did the right thing with the other forms ;)

Collectives™ on Stack Overflow

Excel VBA - Userform vlookup error handling

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related