VLookup returns #N/A

FlynnLOL

New Member
Joined
May 19, 2012
Messages
3
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:
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))
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
VLOOKUP will only look to the right of a lookup value. Maybe

Code:
cusNo = Application.Index(Application.Match(lstCustomer.Value, Sheets("Customers").Columns("A"), 0))
 

FlynnLOL

New Member
Joined
May 19, 2012
Messages
3
VLOOKUP will only look to the right of a lookup value. Maybe

Code:
cusNo = Application.Index(Application.Match(lstCustomer.Value, Sheets("Customers").Columns("A"), 0))

Thanks for the reply, but it seems to be giving me an "invalid number of arguments" error :confused:
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Sorry. Maybe

Code:
cusNo = Application.Index(Sheets("Customers").Columns("A"), Application.Match(lstCustomer.Value, Sheets("Customers").Columns("B"), 0))
 

FlynnLOL

New Member
Joined
May 19, 2012
Messages
3
Sorry. Maybe

Code:
cusNo = Application.Index(Sheets("Customers").Columns("A"), Application.Match(lstCustomer.Value, Sheets("Customers").Columns("B"), 0))
Much thanks VoG, I actually just figured it out as I read this by looking up Index/Match functions myself :)

I actually was not aware that VLookup only looks to the right of the lookup value. That was definitely the case here.

I guess that's the kind of things you overlook when you've only been learning VBA for a couple weeks :P Anyways, thanks again mate :D
 

Forum statistics

Threads
1,148,177
Messages
5,745,194
Members
423,932
Latest member
pablo2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top