Hello there,
I'm trying to do a VLookup in Excel VBA but it doesn't seem to be working properly. Here's the setup:
In a worksheet named "Customers" I have a table of the customer's ID number (Column A) and his/her name (Column B) and other details in the following columns (eg. State in column C, etc.)
In a UserForm, I have a listbox with all the customer names and a button called "Retrieve" which when clicked will retrieve the customer's ID number and display it in a MsgBox. However, the VLookup never seems to work properly despite working when I use another lookup value such as the customer's state.
The code giving me an error (#N/A) is as follows:
lstCustomer is the listbox's name and the customer's ID number is in column 1.
I've been at this for 30 minutes and I can't figure out why. Customer names are in the format of [first name] [last name] such as Jim Smith. When I try to do the VLookup in reverse, using the customer's ID number as the lookup value, it works:
Will properly return a value.
The stranger thing is, on a separate userform where I coded a button to retrieve company product prices, I used the exact same codes and methods (with appropriate variables changed) and it works.
Feel free to ask for clarification and such. Thanks
I'm trying to do a VLookup in Excel VBA but it doesn't seem to be working properly. Here's the setup:
In a worksheet named "Customers" I have a table of the customer's ID number (Column A) and his/her name (Column B) and other details in the following columns (eg. State in column C, etc.)
In a UserForm, I have a listbox with all the customer names and a button called "Retrieve" which when clicked will retrieve the customer's ID number and display it in a MsgBox. However, the VLookup never seems to work properly despite working when I use another lookup value such as the customer's state.
The code giving me an error (#N/A) is as follows:
Code:
Dim cusNo As Variant
cusNo = Application.VLookup(lstCustomer.Value, Sheets("Customers").Range("A2").CurrentRegion, 1, False)
MsgBox CStr(cusNo)
lstCustomer is the listbox's name and the customer's ID number is in column 1.
I've been at this for 30 minutes and I can't figure out why. Customer names are in the format of [first name] [last name] such as Jim Smith. When I try to do the VLookup in reverse, using the customer's ID number as the lookup value, it works:
Code:
cusNo = Application.VLookup(Val("004"), Sheets("Customers").Range("A2").CurrentRegion, 1, False))
The stranger thing is, on a separate userform where I coded a button to retrieve company product prices, I used the exact same codes and methods (with appropriate variables changed) and it works.
Feel free to ask for clarification and such. Thanks