VBA Vlookup reference to number more than 10 digits

NDMDRB

Board Regular
Joined
Jun 20, 2016
Messages
164
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I have a worksheet that contains Product details (BarCode, Name, Category...)

I have the following code to lookup the name reference to the entered BarCode

It works well if the number is less than 10 digits

Ex: if I entered this number in the "txtBarCode", it will lookup the name and all details, but if the number is "1234567899" (10 digits, the name box will be null)

Can someone help me fixing this problem please?

I tried both ways (Find & Vlookup)

Private Sub txtBarCode_Change()

With Sheet1.Range("A4").CurrentRegion
Set fvalue = .Find(What:=txtBarCode.value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
End With


With Me
.txtItemName = fvalue.Offset(, 1).value
End With


'With Me
'.txtItemName = Application.WorksheetFunction.VLookup(CLng(Me.txtBarCode), Sheet1.Range("A4").CurrentRegion, 2, 0)
'End With

End Sub
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hello NDMDRB,

First do not use the Change Event for this. The reason is you want to validate the entire barcode after it has been entered. The Change event happens every time a key is pressed.

TextBox values always return Strings. Even if the characters are all numbers, VBA only recognizes the input as characters and not as numbers. You have to coerce the string value into a number.

The Range.Find method returns either a match or the special object Nothing if it fails. You should always test your Range variable after Find to prevent an error from occurring.

The number value need for a digit or even 12 digit barcode is the Currency Type. This provides you with 15 integers plus 4 decimals.

Delete your Change Event code and replace it with this...
Code:
Private Sub txtBarCode__AfterUpdate()


    Dim BarCode As Double
    Dim fvalue  As Range
    
        With Sheet1.Range("A4").CurrentRegion
            ' // No barcode entered.
            If txtBarCode = "" Then Exit Sub
            
            ' // Coerce the text to a Currency number type. This type is 15 digits + 4 decimal.
            BarCode = CCur(txtBarCode.Value)
            
            Set fvalue = .Find(What:=BarCode, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
            
            ' // No match found.
            If fvalue Is Nothing Then Exit Sub
            
            Me.txtItemName = fvalue.Offset(0, 1).Value
        End With


End Sub
 
Upvote 0
Thank you so much Leith Ross for your time,

This code works fine if the BarCode is 10 digits or less, but didn't work for 11 digits or more (The "txtItemName" still nothing)

I changed the event to AfterUpdate, but still doesn't work

Can you help me again please?
 
Upvote 0
Hello NDMDRB,

I can help you better if you could either send me a copy of the workbook or post it to a file sharing site where I can download it. Would either option be possible?
 
Upvote 0
Hello Leith Ross,

Sure I can, but the file is still mess, because I'm still testing everything before I finalize it

Here is some explanation of what I need, but if it's not enough I will make another file putting all what I need and share it

In my userform, I have a textbox "txtBarCode" another one "TxtItemName"....

What I need in Sub txtBarCode_AfterUpdate is
Once I put any Barcode from the list below Range(A:A), get the related Name from Range(B:B)....

Your code is work well if I entered the first three Barcodes (1 / 123456789 / 1234567899) (Which is just 10 digits numbers)

But if I entered the fourth one "12345678999" it will not get the related name, the txtItemName keeps null )

Any entered number that has 11 or more digits the code will not get the related name

ABCD
BarCodeNameCategorySell Price
11Test1Food 1,000 L.L.‏
2123456789Test2Food 2,000 L.L.‏
31234567899 Test3Food 3,000 L.L.‏
412345678999 Test4Food 4,000 L.L.‏
5123456789999 Test5Food 5,000 L.L.‏

<colgroup><col span="4"><col></colgroup><tbody>
</tbody>

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>


Please let me know if this is enough or should I send you the file

Many thanks in advanced
 
Upvote 0
Hello NDMDRB,

If I could see the complete file that would be best.
 
Upvote 0
Hello Leith Ross,

sorry for the late reply, but a faced a big errors in my workbook and just finished them, and I added your code on a new userform and tested it, so it worked so fine, I think there was a mistake with my code and now everything is fine

Now I'm wondering how I can use the change event or similar, As you said (First do not use the Change Event for this. The reason is you want to validate the entire barcode after it has been entered. The Change event happens every time a key is pressed.) Now I'm using the BarCode Scanner, but it enters each number separate, unless I copied and pasted any number it works fine

But if I changed it to AfterUpdate, every time I'll re-select the "txtBarCode" to enter other BarCode, and this will take time since I need to add hundreds of items daily

I tried to add some lines at the end of my code, like:
Me.txtBarCode.SetFocus

or

Me.txtItemName.SetFocus
Me.txtBarCode.SetFocus

And many more, but didn't get what I need

After I scanned any Item, is there any way to set the "txtBarCode" ="" , and select "txtBarCode" again?

I really appreciate your time and helping me
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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
Back
Top