Help with scanning barcodes into an excel invoice

[AD4GC]

Hi all,
I am looking for help, I have searched through the end of google and my mind is fried.
I have a honey and honeybee business, I have built an invoice I would like to use, I also have compiled a second worksheet in the same workbook as the invoice for UPCs and SKU barcodes. On the second sheet for UPCs and SKU barcodes, Column A has the UPC/SKU in text format, Column B has an item description. Column C has the price, and Column D specifies whether to add Tax with the letter "T".
In my invoice, Columns A thru D are the same, (UPC/SKU, Description, Price, Tax), with Column E being quantity, and Column F as Line Total.

I have searched and haven't found anything that works so far. I have several other macros in this workbook, and I've tried several I've found and no luck so far. I'm not up-to-speed enough to try to make major changes to what I've found, so I'm turning here for assistance.

My invoice item lines start at cell A13 and go down to cell A44, I would like to select cell A13, scan a barcode, cell B13 populates the description, cell C13 populates the item price, cell D13 determines tax specification, and cell E13 populates the quantity then go to cell A14. (I already have a formula to calculate the line totals, Tax calcualtions, Fees, Grand Total, etc.)

Suppose I scan barcode 123456 into cell A13, I want it to pull the details from the UPC/SKU row that match the scanned barcode on the UPC/SKU sheet, then with carriage return set on the scanner, cell A14 is selected, then suppose I scan barcode 123456 again, I would like the quantity in cell E13 to increase by 1 instead of adding a second line with the same product.

I appears that I can only attach a screenshot and not my worksheets, if there is another way that I'm finding please let me know.

Can someone a lot smarter than me help me figure this out?

Thanks,
Greg

 

[rpaulson]

on a copy of your file, add the following code to you invoice worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A13:A44")) Is Nothing And Selection.Count = 1 Then
    
    Item = Target.Value
    
    If WorksheetFunction.CountIf(Range("A13:A44"), Item) <= 1 Then Exit Sub 'exit sub if this is the only item on the invoice
    
    r = Range("A12:A44").Find(Item, Range("A12"), , xlWhole).Row
    Cells(r, "E") = Cells(r, "E") + 1
    Target.ClearContents
End If
End Sub

hth,
Ross

 

[AD4GC]

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A13:A44")) Is Nothing And Selection.Count = 1 Then

Item = Target.Value

If WorksheetFunction.CountIf(Range("A13:A44"), Item) <= 1 Then Exit Sub 'exit sub if this is the only item on the invoice

r = Range("A12:A44").Find(Item, Range("A12"), , xlWhole).Row
Cells(r, "E") = Cells(r, "E") + 1
Target.ClearContents
End If
End Sub

This does work, however I have to scan the barcode twice to make the quantity show "1".

If WorksheetFunction.Countif(Range("A13:A44"),Item)<=1

If I change <=1 to <=0, I get a quantity of 1 in cell E13with the first scan, but my barcode text is deleted from cell A13, I tried removing the

Target.ClearContents

line, it would leave the barcode text, but added duplicates for each scan of the barcode text in the following cells below, but it did increase the quantity total in E13 by how many times it was scanned.

I apologize for being so ignorant of this matter, but I know very little about it, but I'm certain there is a way to make it work...

Thanks
Greg

 

[rpaulson]

Greg,

change
If WorksheetFunction.CountIf(Range("A13:A44"), Item) <= 1 Then Exit Sub 'exit sub if this is the only item on the invoice
to
If WorksheetFunction.CountIf(Range("A14:A44"), Item) <= 1 Then Exit Sub 'exit sub if this is the only item on the invoice

let me know how it works out.

 

[AD4GC]

By changing
If WorksheetFunction.CountIf(Range("A13:A44"), Item) <= 1
to
If WorksheetFunction.CountIf(Range("A14:A44"), Item) <= 1

I still got the same result, except it took three scans for the quantity to show 1...

 

[rpaulson]

What happens if you type the item number in the cell instead of using the bar code reader?

 

[AD4GC]

What happens if you type the item number in the cell instead of using the bar code reader?

It returns the same result.

 

[rpaulson]

Greg,
not sure why it act different on your file than it does on mine. if you can upload your file somewhere (Drop Box, one drive, mega file etc...) then i can to take a loot at it.

be sure to remove any confidential information.
-Ross

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Help needed for barcode scanner macro
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[NoSparks]

@AD4GC
I had downloaded your file from that other site but can't get there now, can see there's been 2 responses, the last by ByteMarks, but can't open the thread.
Seeing that I did spend some time on this I'll post what I came up with
See if this does what you're after.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' limit to single cell change
If Target.CountLarge > 1 Then Exit Sub

Dim fndRng As Range, FindString As String
Dim arr As Variant

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

' limit to specific range
If Not Intersect(Target, Range("A13:A44")) Is Nothing Then
    ' if changed to blank
    If Target.Value = "" Then
        ' move everything up to that row
        arr = Range(Cells(Target.Row + 1, 1), Cells(44, 5)).Value
        Target.Resize(44 - Target.Row, 5).Value = arr
        Range("A44").End(xlUp).Offset(1).Select
        GoTo Enablethings
    Else
    ' deal with the scan
        FindString = Target.Value
        ' if it already exist on invoice
        If WorksheetFunction.CountIf(Range("A13:A44"), FindString) = 2 Then
            'find the first one and add 1 to quantity
            Set fndRng = Range("A12:A44").Find(What:=FindString, _
                                            After:=Cells(12, 1), _
                                            LookIn:=xlValues, _
                                            LookAt:=xlWhole, _
                                            SearchOrder:=xlByRows, _
                                            SearchDirection:=xlNext, _
                                            MatchCase:=False)
            If Not fndRng Is Nothing Then
                ' add 1 to existing quantity
                fndRng.Offset(, 4) = fndRng.Offset(, 4).Value + 1
                ' remove the current scan
                Target.Value = ""
                Target.Select
            End If
        
        Else    'add to invoice
            'check for scan in UPC-SKU column A
            With Sheets("UPC-SKU").Range("A:A")
                Set fndRng = .Find(What:=FindString, _
                                After:=.Cells(2, 1), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                If Not fndRng Is Nothing Then
                    Target.Offset(, 1).Resize(, 3).Value = fndRng.Offset(, 1).Resize(, 3).Value
                    Target.Offset(, 4) = 1
                    Target.Offset(1).Select
                Else
                    MsgBox FindString & "  was not found"
                    Target.Select
                End If
            End With
        End If
    End If
End If
    
    ' unhide another row as necessary
    If Target.Row > 17 And Target.Row < 43 Then
        Target.Resize(3).EntireRow.Hidden = False
    End If

Enablethings:
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub

I can foresee an issue should a scan go overtop of an existing scan but for right now I'm not going to deal with that until I know what else is happening with this.