Results 1 to 7 of 7

Thread: VBA Vlookup reference to number more than 10 digits
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2016
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Vlookup reference to number more than 10 digits

    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

  2. #2
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,850
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Vlookup reference to number more than 10 digits

    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
    Sincerely,
    Leith Ross

  3. #3
    Board Regular
    Join Date
    Jun 2016
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Vlookup reference to number more than 10 digits

    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?

  4. #4
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,850
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Vlookup reference to number more than 10 digits

    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?
    Sincerely,
    Leith Ross

  5. #5
    Board Regular
    Join Date
    Jun 2016
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Vlookup reference to number more than 10 digits

    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

    A B C D
    BarCode Name Category Sell Price
    1 1 Test1 Food 1,000 L.L.‏
    2 123456789 Test2 Food 2,000 L.L.‏
    3 1234567899 Test3 Food 3,000 L.L.‏
    4 12345678999 Test4 Food 4,000 L.L.‏
    5 123456789999 Test5 Food 5,000 L.L.‏


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

    Many thanks in advanced

  6. #6
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,850
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Vlookup reference to number more than 10 digits

    Hello NDMDRB,

    If I could see the complete file that would be best.
    Sincerely,
    Leith Ross

  7. #7
    Board Regular
    Join Date
    Jun 2016
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Vlookup reference to number more than 10 digits

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •