worksheetfunction.vlookup result when argument not found

conor

New Member
Joined
Oct 19, 2006
Messages
3
I am trying to execute the piece of code below. The user initials (UserInits) have already been successfully entered thru an InputBox. The initials are then checked against a range (Users) in the workbook to determine what the matching entry should be in the next InputBox which will ask for the password. This code works if the vlookup finds a match on user initials. If it does to find a match I get "run-time error 1004 application-defined or object-defined error". The watch window tells me the value of password has become <out of context>. I expected it to return #N/A as it does on a worksheet, but it doesn't.


password = WorksheetFunction.VLookup(UserInits, Users, 2, False)


Does anyone have any input. Thanks in advance
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Why don't you use a userform rather than inputboxes?

Then for the user initials you could use an combobox populated from the spreadsheet.

This would make sure users could only select existing values.

For the password you could use a textbox, and you would have the added advantage of being able to hide the password using it's PasswordChar property.
 

conor

New Member
Joined
Oct 19, 2006
Messages
3
to Norie

Thanks for the quick input. I'm not VB guru, so I will have to investigate the userform. I really don't know what that is. But this is the second time I have tried to write a function using a call to the vlookup function and the second time I have failed for the very same reason - it does not seem to return the value #N/A when the search argument is not found. Do you by any chance know what IS returned so I can validate it?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Using VLookUp, or any function that can produce errors, in VBA can be difficult.

I don't actually know what's returned when a value isn't found, I rarely if
ever use VLookUp in VBA.

I'll try and find out and post back.

Meanwhile if you want to try the userform idea try this.

1 Goto the VBA Editor.(ALT+F11)

2 Goto Insert>UserForm.

3 From the toolbox click on combobox then draw one on the form.

4 Do the same for a textbox.

5 Goto View>Properties.

6 Select the combobox, set the ColumnCount to 2 and the RowSource to Users.

7 Select the textbox and set the PasswordChar property to *.

8 Now run the userform from the Run menu.

You should then see a userform that has a combobox listing in one column user initials and in another the passwords.

If you type anything in the textbox all you will see will be *s.

Note this is only a start, you'll probably want to stick a couple of buttons on the form too and you probably want to hide the passwords.

By the way this assumes Users is a named range with 2 columns, one for initials and one for password.
 

conor

New Member
Joined
Oct 19, 2006
Messages
3
Dude,

That is way more help than I ever expected on a public forum! You are THE MAN (or THE WOMAN)! Norie isn't a name I can associate with either gender. Thank you A LOT. And yes, the lookup range is only 2 columns. I'll test this out as soon as I say thank you. First things first.

Thank you.

conor
 

Forum statistics

Threads
1,137,366
Messages
5,681,072
Members
419,950
Latest member
BeckiJae

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