Barcode scanning with Excel - Page 12
Attend Excelapalooza
Thanks Thanks:  0
Likes Likes:  0
Page 12 of 12 FirstFirst ... 2101112
Results 111 to 120 of 120

Thread: Barcode scanning with Excel

  1. #111
    Board Regular
    Join Date
    Oct 2012
    Posts
    4,514
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Barcode scanning with Excel

     
    Quote Originally Posted by zmunns View Post
    It is still spitting out the error message... I ran the accuracy test and the outputs are the same yet when the number is plugged into the "scan output" sheet, it is not liking it. Do you have any ideas to fix this?

    Thanks

    Zach
    How about posting a link to the workbook where the errors are occurring, along with some actual barcode scan values.

    I will use those barcodes scans in a drop down in cell A2 to see if I can figure out what's going on.

    We are scanning into B2 first, then A2 second... correct?

    Howard

  2. #112
    New Member
    Join Date
    Nov 2017
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Barcode scanning with Excel

    Here is a link to the workbook with some actual barcode scan values on the "scan output" sheet.

    https://drive.google.com/file/d/15XX...ew?usp=sharing

    That is correct. I look forward to hearing your thoughts. It seems when the item is typed in it is spit out as a wrong number, whereas when the value is copied and pasted in, it works

    Zach

  3. #113
    Board Regular
    Join Date
    Oct 2012
    Posts
    4,514
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Barcode scanning with Excel

    Quote Originally Posted by zmunns View Post
    That is correct. I look forward to hearing your thoughts. It seems when the item is typed in it is spit out as a wrong number, whereas when the value is copied and pasted in, it works

    Zach
    My observations are...
    Cells A2 and B2 have drop downs.
    A2 is formatted as Number, showing two decimal points.
    Cell N4 and N7 on sheet Data Input each has a trailing space "QA " should be "QA" and "SHIP " should be "SHIP"

    This works for me on the workbook you linked.

    B2 data is entered first, then A2 second.
    I dragged both drop down in A2 and B2 out the action cell locations. A2 is now formatted as General (as is B2), neither has a drop down.
    Typing into OR copy and pasting into A2 any of the values you show as examples all work as expected for me.
    B2 entries function as they should also.

    The linked workbook apparently is not your actual workbook so if you duplicate your actual to this test workbook, I cannot see why it would not work for you.

    I also noticed there was a space missing in the alert message where:

    This - MsgBox "No " & myFndX & "match found."

    Should be this - MsgBox "No " & myFndX & " match found."


    Howard

  4. #114
    New Member
    Join Date
    Nov 2017
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Barcode scanning with Excel

    So if you're keying in the barcode number into the A2 cell, it was working for you? I just tried it and it didn't seem to want to work for me... B2 works just fine, it's the input into cell A2 that has been giving me some trouble

  5. #115
    Board Regular
    Join Date
    Oct 2012
    Posts
    4,514
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Barcode scanning with Excel

    Quote Originally Posted by zmunns View Post
    So if you're keying in the barcode number into the A2 cell, it was working for you? I just tried it and it didn't seem to want to work for me... B2 works just fine, it's the input into cell A2 that has been giving me some trouble
    Yes, typing in the B2 entry like HEM and then typing in one of the example numbers like 4635349 into A2 works just fine.

    What do you mean... "I just tried it and it didn't seem to want to work for me..." what is not working?
    Do you get the "No Match" alert?
    Is it not returning the correct data to the other worksheet?
    What exactly is happening.
    Are you using a different workbook than the one you linked in post #112 ?

    Howard

  6. #116
    New Member
    Join Date
    Nov 2017
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Barcode scanning with Excel

    The B2 input is working just fine, but every time I type a number into the A2 cell I'm getting the error message saying "the number you entered is not valid"

    In your reference above, I type in 4635349 to A2 and immediate get the error message. I then check the "inventory status" page and find that it's populate a scan despite the error being recorded. Although the data is recorded, it does not match up to the "data input" table. The first line below is the output in "inventory status". The second line is the data 4635349 is associated with in the "data input" table.


    Hope you can help figure this out! Thanks again for everything


    496342 US CS ANSI 3IN1 SHELL S ANSI YE 4 Hemming (Pants) 12/1/2017 19:51

    4635349 481889 WR CS PENCIL SKIRT 16 PAS PLU AW Female Tailored-skirt 10/22/17 82576










  7. #117
    Board Regular
    Join Date
    Oct 2012
    Posts
    4,514
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Barcode scanning with Excel

    Copy and paste the code that is giving you these inaccurate results you show. That code would be in the sheet module for the sheet that you are scanning your numbers into B2 and A2.

    I want to see that code, you say... "I'm getting the error message saying "the number you entered is not valid" and that is different from the error warning for a mismatch in the workbook you posted a link to in Post #112 .


    [QUOTE][Are you using a different workbook than the one you linked in post #112 ?/QUOTE]

    You did not answer this question I asked of you in post #115.
    It is important that we are both seeing and using identical workbooks. If you are using a different workbook than the one you linked in #112 then I cannot help. It all works fine for me with the linked workbook.

    Howard

  8. #118
    New Member
    Join Date
    Nov 2017
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Barcode scanning with Excel

    Code:
    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("$A$2")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    Dim LRow As Long, LRowX As Long
    Dim aRng As Range, rngFnd As Range, rngFndX As Range
    Dim myFnd As String, myFndX As String
    myFnd = Target
    myFndX = Target.Offset(, 1)
      If myFnd = "" Then
        Exit Sub
      ElseIf IsNumeric(myFnd) Then
        myFnd = Val(myFnd) '/ converts a "text" number to a value
      Else
        '/ is text and that is okay
    End If
       With Sheets("Data Input")
        LRow = Sheets("Data Input").Cells(Rows.Count, "A").End(xlUp).Row
       
        Set rngFnd = Sheets("Data Input").Range("A2:A" & LRow).Find(What:=myFnd, _
                         LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                         SearchDirection:=xlNext, MatchCase:=False)
                         
        If Not rngFnd Is Nothing Then
      
           rngFnd.Resize(1, 6).Copy Sheets("Inventory Scan").Range("A" & Rows.Count).End(xlUp)(2)
           Sheets("Inventory Scan").Range("A" & Rows.Count).End(xlUp).Offset(, 6) = Format(Now(), "yyyy-mm-dd hh:mm:ss")
           
           rngFnd.Offset(, 1).Resize(1, 5).Copy Sheets("Inventory Status").Range("A" & Rows.Count).End(xlUp)(2)
           Sheets("Inventory Status").Range("A" & Rows.Count).End(xlUp).Offset(, 6) = Format(Now(), "yyyy-mm-dd hh:mm:ss")
           
          Else
            MsgBox "No " & myFnd & " match found."
               
          End If
          
          
               LRowX = Sheets("Data Input").Cells(Rows.Count, "N").End(xlUp).Row
       
          Set rngFndX = Sheets("Data Input").Range("N3:N" & LRowX).Find(What:=myFndX, _
                         LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                         SearchDirection:=xlNext, MatchCase:=False)
       If Not rngFndX Is Nothing Then
        
             rngFndX.Resize(1, 2).Copy Sheets("Location Scan").Range("A" & Rows.Count).End(xlUp)(2)
             rngFndX.Offset(, 1).Copy Sheets("Inventory Status").Range("A" & Rows.Count).End(xlUp).Offset(, 5)
             Sheets("Location Scan").Range("A" & Rows.Count).End(xlUp).Offset(, 2) = Format(Now(), "yyyy-MM-dd hh:mm:ss")
           Else
            MsgBox "No " & myFndX & "match found."
     
          End If
        End With
      
    [A2].Activate
    End Sub
    Above is the code that is in the sheet module as requested. Here is the Gdrive link to the spreadsheet I'm using https://drive.google.com/open?id=15X...wFJipdRFF7nRiW

    Here is a screen grab that shows the error code I'm getting despite using validated values from the "data input" tab https://drive.google.com/file/d/1O1i...4ahcbaWFZ/view

    I hope this helps

    Thanks

  9. #119
    Board Regular
    Join Date
    Oct 2012
    Posts
    4,514
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Barcode scanning with Excel

    A couple of things... I don't know why the drop down would show an error with what appears to be a valid Inv Scan number.

    On sheet Data Input in range N3:N7 there are a couple of trailing spaces in the Location abbreviations. Remove them.

    On your actual sheet you will not be using drop downs in A2 and B2 (I use them to simulate a scan). I deleted the current drop downs and reinstalled them using the five example scan numbers in column F and the Location abbreviations in column G. All worked fine as far as transferring data to their proper locations, notwithstanding the following.

    The biggest problem I see is the numerous duplications on sheet Data Input column A.

    Put this formula in cell M3 and fill down to row 3197. You have about 60 +- duplicates values in column A.

    =IF(COUNTIF($A$3:$A$3197, $A3)>1, "Duplicate", "")
    Remove the duplications and recheck the sheet Scan Outouts operation.
    Also, your data in the columns F and L appears to be incomplete from row 3143 on down.

    Once you have your data cleaned up, see if this clears the issues.

    Howard

  10. #120
    New Member
    Join Date
    Feb 2018
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Barcode scanning with Excel

      
    Hi there.

    I just wanted to note, that it's also possible to scan barcodes into Excel without a barcode scanner.
    You can use your iOS or Android phone/pad and scan the barcodes with Scan-IT to Office directly into your Excel sheet.
    Since this app uses cloud services, you don't need to be at your computer to do that.

    Scan-IT to Office - App: https://itunes.apple.com/app/scan-it...e/id1241932661
    Scan-IT to Office - Office Add-In: https://appsource.microsoft.com/prod...ce/WA104381026

    greetings

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
  •  

 

 
DMCA.com