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

 

[AD4GC]

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

That code does not work on my end.

However, rpaulson did send me a solution. I'll let him decide whether to share the code or not, but thanks for the help.

 

[NoSparks]

That code does not work on my end.

Interesting, I just downloaded your file again from that other forum and replaced all the code on the Invoice sheet module
with a pasting of the code from post10 and it works for me.
I will admit the only way I can imitate an entry by your barcode scanner is to copy a barcode from the UPC-SKU sheet
and paste it to the Invoice with Ctrl+v

 

[RoryA]

However, rpaulson did send me a solution. I'll let him decide whether to share the code or not, but thanks for the help.

Kindly note that it is against forum rules to take questions off the forum. Please share the solution here for the possible benefit of others, and make sure not to repeat this.