Help with scanning barcodes into an excel invoice

AD4GC

New Member
Joined
Feb 2, 2017
Messages
6
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
 

Attachments

  • Invoice.jpg
    Invoice.jpg
    210.6 KB · Views: 28
  • UPC-SKU.jpg
    UPC-SKU.jpg
    161.4 KB · Views: 24
@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.
VBA Code:
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.
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top