I created an inventory worksheet with userforms. Now I have a problem with VLookup command. I want the name of the item is shown in the "txtName" after I scanned the barcode in the "txt2DBarcode."
The second part of the code is working and can find the name of the item. But if the barcode number does not exist in the Sheet1.Range "A:A" I have debug error instead of showing the message.
Here is the code:
Untitled.jpgHTML Code:
oughta be
Please Login or Register to view this content.
Please Login or Register to view this content.
Josie
if at first you don't succeed try doing it the way your wife told you to
Yeyyyyy. It is working. Thank you Josie.
'maybe like this
Please Login or Register to view this content.
Will work also.
Please Login or Register to view this content.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
Thank you all for your help. It is interesting that the code is working perfectly for one Userform (Received), but for the other userform in the same spreadsheet is not. The only different is "txt2DBarcode" changed to "txt2DBarcode_A".
Run-time error '1004': Unable to get the VLookup property of the worksheetFunction class.
HTML Code:
I was wrong. I can see this error on my all userforms.
Untitled1.jpg
countif will convert your values to a number if possible (15 significant digits only) and look for that. vlookup will not convert and will look for text. if your actual data is numbers formatted to show the leading zero then that would explain it.
It may be that leading zeroes are being removed. Try adding a leading single quote to prevent autocasting of the string: Add
"'" &
before the Left, like:
.... VLookup("'" & Left(txt2DBarcode.Text,16),......_
Bernie Deitrick
Excel MVP 2000-2010
Still I have debug.Originally Posted by Bernie Deitrick
Any chance you could upload a sample workbook?
Click on GO ADVANCED, scroll down and click Manage Attachments.
If posting code please use code tags, see here.
Thank you Josie. What is your recommendation for my code?
Below is the screenshot of sheet1.
Capture.JPG
The sample is attached. You can test "Received Vaccine" form with this barcode: 01003000648270191719081010N021719
It must show "Varicella Varivax" as name of item. And if you change the first 16 digit number must alert. But it is not working.
Last edited by S.Nik; 01-26-2018 at 09:36 AM.
Why are you using Val in the VLookup?
This worked for me.
Please Login or Register to view this content.
Why it is not working for me?Why are you using Val in the VLookup?
This worked for me.
Please Login or Register to view this content.
Untitled.jpg
When you get the error what code are you using?
PS Have you considered using a combobox for the codes rather than a textbox?
Last edited by Norie; 01-25-2018 at 05:11 PM.
Two mistakes - you were using Val, which would remove the leading zeroes, and your sheet name is Vac_List, but you were treating it as a range name. This works:
Capture.JPGPlease Login or Register to view this content.
[QUOTE=Bernie Deitrick;4829287]Two mistakes - you were using Val, which would remove the leading zeroes, and your sheet name is Vac_List, but you were treating it as a range name. This works:
In fact I have a worksheet and range name as "Vac_List." Do you think that causes the problem?Please Login or Register to view this content.
Thank you for the screenshot but did you try to send it too? Because after I send it and see the message that "Data added" I see error.
Capture.JPG
In my experience, it is never a good idea to have two different things with the same name. In any event, your sheet has an underscore in its name, so at the least you should use
Please Login or Register to view this content.
Please don't cross-post without providing a link to the thread on the other forum.
[QUOTE=Bernie Deitrick;4829310]In my experience, it is never a good idea to have two different things with the same name. In any event, your sheet has an underscore in its name, so at the least you should use
You are right. I am new in VBA world. I thought it is less complecated if I pick a name for both. I tried your recent code and I see error after I hit save:
Capture.JPG
You've re-introduced the error that we fixed before - after the code is cleared from the text box "txt2DBarcode" the txt2DBarcode_afterupdate code is run again and you are not properly handling the null string case. Try it like the code below, where if the text of that textbox is "" then nothing is done (the code only works is the value is not "").
We had added
Application.EnableEvents = False
'....
Application.EnableEvents = True
around this section:
'Clear the data
txt2DBarcode_A.Value = ""
but it is not there anymore....
Please Login or Register to view this content.
Thank you very much Bernie. You made my day. The code is working. You are awsome!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks