Userform vlookup - error when name not in spreadsheet

**glynnhicks** · 05-10-2013, 08:31 AM

Hi all,

Wondered if anyone can help, I've been going through the forums to see if I can find help but nothings coming up.

I have a userform where I choose an employees name from a combobox, then use a vlookup to pull corresponding data from a spreadsheet such as department, etc.. That works works great thankfully, but if I type in an employees names into the combobox that's not in the spreadsheet I get the attached message.

Here is the code I use for the vlookup:-

var1 = Application.VLookup(EmployeeName.Value, Worksheets("Manning List").Range("Manning_list"), 4, False)

SAPNumber.Value = var1

var2 = Application.VLookup(EmployeeName.Value, Worksheets("Manning List").Range("Manning_list"), 3, False)

Staff_Agency.Value = var2

I've tried to use a if else in the code to do nothing in the eventuality that the code doesn't find the name as it shouldn't exist but I'm getting nowhere.

Lack of knowledge unfortunately, so if anyone can help I'd be V grateful.

Thanks
Glynn

**Tinbendr** · 05-10-2013, 12:19 PM

Could you load the combobox with the employee names from the sheet, instead? This way, you will always have all of the names in the list in the dropdown.

And because a combobox/listbox is a visual array, you can load the SAP# and the Staff Agency as 2nd and 3rd elements of the combobox. This means that when you choose from the dropdown, the other elements are already loaded. You don't need to go back to the sheet to search for anything.

**Norie** · 05-10-2013, 12:23 PM

Glynn

If you type in a name that's not on the combobox list then the ListIndex of the combobox will be -1, so you can check for that before trying the lookup.

Please Login or Register  to view this content.

By the way, you might be able to use the ListIndex to find the row for the selected employee.

**glynnhicks** · 05-14-2013, 03:43 AM

Hi both,

Sorry for not replying sooner I was away from my PC, Norie thanks for the code, it initially works but then I seem to get the attached error message, would appreciate any help?

Thanks again both, appreciate your time.

Glynnerror message.gif