Help with scanning barcodes into an excel invoice

A

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

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
on a copy of your file, add the following code to you invoice worksheet.

VBA Code: 
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
 
Upvote 0
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
Click to expand...

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

VBA Code: 
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
VBA Code: 
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
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
What happens if you type the item number in the cell instead of using the bar code reader?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
@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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

SkywardPalm
  • Question
Slow Typing Response from Bluetooth Scanner
Replies
3
Views
721
Candyman8019
C
A
  • Solved
Pivot Table Sum giving Zeros but count works
Replies
5
Views
810
RoryA
RoryA
D
  • Question
Barcode scanning tool
Replies
0
Views
797
D3FCON
D
L
  • Solved
VBA code for formatting certain cells to include a border
Replies
6
Views
259
mumps
mumps
K
  • Solved
Dynamic Sum Range Addresses...(?)
Replies
2
Views
146
Kenny_D
K

Forum statistics

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

Share this page

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