Multi-quantity inventory with scanner

[fmx8329]

Hey all,

I am pretty new here. I looked at a few of the threads related to inventory but none would do what I want to do. Basically, I want to scan a box that will have shirts in it. The box will have a barcode with the item number on it and then a barcode with the quantity. I was hoping to figure it out so that when the item number was scanned, excel would jump to that item column and the the quantity in the box would be added underneath to keep a running total.

We are trying to inventory a warehouse with many boxes of shirts (typically 80-120 shirts per box) We need to run inventory about once a week and we need a pretty automated system to scan the entire warehouse. Any ideas where to start? I figured out vlookup and sumif but they didn't seem to want to work together. I just want to simplify everything so I don't have to do any sorting and organizing after i scan the entire warehouse.

Thanks for any and all help!

 

[Sal Paradise]

How do you get the barcode data from the scanner to excel?

If you can get the barcode (and/or item number) into Excel, you can write a simple macro to pop up an input box whenever something is scanned to enter quantity, and then automatically put that in the table.

(you will need a macro though most likely)

 

[fmx8329]

The barcode scanner plugs into my USB port and it automatically writes in whatever excel cell I am currently in when I scan an item. Any ideas of what type of macro this would need? I am using Excel 2007 for Windows 7.

 

[Sal Paradise]

I would protect the sheet so that only one cell can be selected, then have an "OnChange" worksheet event that triggers whenever that cell changes (whenever a new item is scanned).

Then you would pop up an input box, ask for the quantity, make sure it's a number, and insert it in your inventory sheet.

 

[Sal Paradise]

As an example. If you have your inventory set up like this:

Excel 2003
	A	B
1	Item	Quantity
2	0001	20
3	0002	464
4	0003	168
5	0004	110
6	0005	135
7	0006	185
8	0007	339
9	0008	432
10	0009	134
11	0010	257
12	0011	266
13	0012	441
14	0013	40
15	0014	345
16	0015	257
17	0016	334
18	0017	76
19	0018	411
20	0019	422
21	0020	98
22	0021	226
23	0022	393
24	0023	416
25	0024	73
26	0025	449
27	0026	478
28	0027	23
29	0028	402
30	0029	475
31	0030	6
32	0031	436
33	0032	114
34	0033	367
Inventory

And your input sheet like this:

Excel 2003
	A	B	C	D
1	Input	Current Quantity	New Quantity	Text Item
2	0001	20		0001
Input

When you scan something in, it goes into A1, and the following code gets run (put it in the "Input" worksheet code section in VBA):

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim intQuantity, intLastRow As Integer
    
    intLastRow = Worksheets("Inventory").Range("A65535").End(xlUp)
    
    If Target.Address = "$A$1" Then
        intQuantity = MsgBox("Inventory Shows " & Range("$B$2") & " pieces in stock." & vbNewLine & "How many are there?", "Confirm Quantity")
        If IsNumeric(intQuantity) Then
            Range("$C$2").Value = intQuantity
            For i = 2 To intLastRow
                If Worksheets("Inventory").Range("A" & i).Value = Worksheets("Input").Range("$D$2") Then
                    Worksheets("B" & i).Value = intQuantity
                End If
            Next i
        Else
            MsgBox ("Please enter a number.")
        End If
    End If
End Sub

This code could be much cleaner. The point is that it gives you an input box when you scan something, then replaces the old value in your inventory table (there are much better ways to do this, as well as keeping your old records, etc.)

 

[fmx8329]

Thanks Sal,

Would this allow for the same item to be entered multiple times? I was thinking of having it set up so that when you scan the item number on the box it would locate that cell and then when you scan the quantity it would add it in that column. Then if I come up to another box with same item number and quantity in the box I can scan it and it will find the item and then go to that cell and automatically add the next quantity scanned so that I have a running total. Does that make sense? I am also trying to do more research on how to actually make a macro. They don't seem easy, but I am kind of desperate to get this sheet working how I want it.

 

[Sal Paradise]

The way I have it set up will overwrite the previous entry. You can make it additive, or put the date in a cell and have it create a new column and/or sheet for that date and reset all the numbers, or...

There are a billion ways that it could be set up. It depends on what's easiest to use. My suggestion would be to just start thinking up what exactly you want, and take a shot at writing it yourself, asking for help when you get stuck along the way.

If you are truly desperate to get it done in a hurry, you could probably find someone to write it for you (for a fee).