VBA Vlookup reference to number more than 10 digits

[NDMDRB]

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

 

[Leith Ross]

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...

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

 

[NDMDRB]

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?

 

[Leith Ross]

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?

 

[NDMDRB]

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

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][TABLE="width: 354"]
<colgroup><col span="4"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BarCode[/TD]
[TD]Name[/TD]
[TD]Category[/TD]
[TD]Sell Price[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]Test1[/TD]
[TD]Food[/TD]
[TD] 1,000 L.L.‏[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123456789[/TD]
[TD]Test2[/TD]
[TD]Food[/TD]
[TD] 2,000 L.L.‏[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1234567899 [/TD]
[TD]Test3[/TD]
[TD]Food[/TD]
[TD] 3,000 L.L.‏[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]12345678999 [/TD]
[TD]Test4[/TD]
[TD]Food[/TD]
[TD] 4,000 L.L.‏[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]123456789999 [/TD]
[TD]Test5[/TD]
[TD]Food[/TD]
[TD] 5,000 L.L.‏[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]


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

Many thanks in advanced

 

[Leith Ross]

Hello NDMDRB,

If I could see the complete file that would be best.

 

[NDMDRB]

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