Counting Inventory with Barcode Scanner

[RapidFireGT]

Windows 7
Excel 2007
Wasp WWS800 Wireless Barcode Scanner

First off, I'm not even sure if this is possible.

I've searched through 12 pages of "barcode" results and didn't find much, so I'll ask in this thread. Now, what I am about to ask it most likely too heavy for a complete answer, so even if you can just point me in the right direction, I'd appreciate it greatly.

What I will be doing
We are a wholesaler distributor and send out 2-4 pallets every day on each route, and we have about 5 routes everyday. At the end of each day before pallets are loaded onto the trucks, we count the product on the pallets to make sure the orders have been filled accurately. We would like to use a barcode scanner to scan each product and then populate a list of what products and what quantities are on the pallets.

What I would like Excel to do
I would like to set up a template with 3 columns: barcode, product name, and quantity. I would like to scan an item, the barcode scanner then inputs the barcode number into the "Barcode" column, Excel then cross references this number to match it with its corresponding product name which would then be inserted into the "Product Name" column, and then Excel would increase the quantity by 1 in the "Quantity" column.

Basically, I want insert the product number into excel, which then matches it with a product name and starts counting the number of times I scan that particular item.

Here is a sample spreadsheet of what I imagine such spreadsheet would appear as:

Again, I'm not even sure if this is possible in Excel, and I assume it is too much work for you guys just to spit out the formulas to me to accomplish this. With that said, if you can even point me in the right direction to achieving this, I would appreciate it.

Thanks guys!

 

[Misca]

The code I sent you is supposed to be an autoevent macro. It runs automatically whenever you scan a value to the sheet you put the code (= whenever the sheet changes one way or another). The first couple of lines tell you when it doesn't run = If you change more than one cell at a time or if your Target cell happens to be located at the A1 CurrentRegion.

Try it: Name one of the sheets in the Workbook as "Inventory" and put your Barcode values to column A and Product Names to column B on that sheet. Then write the headers to A1:C1 on the sheet you want to use as the operational sheet (= the sheet whose module you put the code in to), select a cell that is not touching the headers (say, E1, for example) and scan a barcode.

What the macro is supposed to do is:
1) Take the barcode value and see if the same value can be found in the column A already. If so, it'll will add 1 to the Quantity cell of that row.
2) If the barcode value can not be found on the sheet already, then it writes the value to the end of A1 CurrentRegion, looks for the ProductName info from the Inventory sheet and puts 1 to the Quantity cell of that new row.

It also clears the cell you just entered the value to so that what you should be seeing is the finished sent products list that's looking just like your desired list on your first post.

What the code is not doing is keeping track of the number or shipments: All it does is checking the barcode you're entering to the page and trying to keep track of those.

When you want to start a fresh list just clear the old values or add a new sheet with the same code in it: The idea is that as long as there's older values found on the sheet it will just keep adding ones to the Quantity cell on column C.

If you want to keep a record of how many of each items have been shipped on each day, add a date to your Quantity header and every day add a new column C (=moving the previous column C one step to the right) and you'll get the shipping history automatically. Just make sure the cell you're scanning the data into is not touching the A1 CurrentRegion and your code should work as it is.

 

[RapidFireGT]

Your code sounds really promising and I thank you for taking the time to write that up for me, but for some reason I can't seem to implement it correctly.

Here I have my workbook set up with Sheet1 (my operational sheet) labeled as "Route Load," and Sheet2 (my reference sheet for the VLOOKUP command) labeled as "Inventory." "Route Load" is displayed below.

Displayed below is my "Inventory" sheet with all of the sample barcodes and their respective sample names. This is the table I am using for the VLOOKUP formula to reference.

Displayed below is where I inserted your code. I opened up VBE, right clicked on my operation sheet "Route Load," selected "Insert," and then selected "Module."

Then I inserted your code and saved it (Module2).

... post continued in the next post. Apparently you can only post 4 images per post.

 

[RapidFireGT]

Then I went back to my operational sheet "Route Load" and tried inserting some sample barcodes as you said, just as if I had been scanning them into Excel in cell E1. As you can see, the sample barcodes were being entered, but nothing was happening.

I'm sure I am doing something wrong here, and hopefully the screenshots will help you to see where I went wrong.

I really appreciate all the help you guys are giving me.

Thanks again!

 

[Misca]

You pasted the code in the wrong module: Click on the Sheet1(Route Load) and paste it in there.

 

[RapidFireGT]

You pasted the code in the wrong module: Click on the Sheet1(Route Load) and paste it in there.

Sorry for the delay, I've been busy accompanying a family member in the hospital and I'm just now getting around to working with this project again.

With that said, I did what you said, and it works!

Thank you so much! This module seems to be doing exactly what I was looking for. Now all I need to do is get busy scanning all of our products and building up the name cross reference database so I can start using this.

Again, thank you so much. I am still amazed by the great advice and help you guys are able to give here on these forums.

 

[Hascler]

thank you for the post and replies I have adopted this to use for my food storage inventory. The question i have is, is there a way to add to this to be able to remove an item by scanning or would I have to manually do that?

 

[jimmyozborn]

Hello to both of you, I commend you on the concerted effort you have both undertaken to complete the "inventory" barcode look up, I am currently involved in a similar project. Will be implementable with the vb code posted, with minor changes A:F:

my situation is this: in addition to using the barcode look up and data population with quantity update I have a secondary reference using sku #, I would like to add a field for manual lookup, and If="no barcode void or null" vlook sku some items only have one or the other, and populate my columns the same as if I used the scanner: here is my current format:

SKU Description Tax Barcode Cost MAP Price QOH
212400121654 Moto Tool Yes 212400121654 5.98 11.95 1

( am new to using forums, and had not been using excel for about seven yrs, I was once upon a time good using 97', i have recently been trying to setup an online hobby shop)

I would greatly appreciate any help adding additional code or supplementing the current code you have already implemented in the current thread discussion, Thank you for the wonderful things I have been able to find

 

[nikzomer]

You pasted the code in the wrong module: Click on the Sheet1(Route Load) and paste it in there.

can help me if i need only 8 digits from the scanner and it will find from inventry only by 8 digits

 

[Misca]

The number of digits used can be manipulated with text functions. You might want to try using a string variable reading the value from the barcode and manipulate that with LEFT, RIGHT or MID functions and then use the edited value with the rest of the code.

 

[nikzomer]

The number of digits used can be manipulated with text functions. You might want to try using a string variable reading the value from the barcode and manipulate that with LEFT, RIGHT or MID functions and then use the edited value with the rest of the code.

thankz alot but i am not familar with the fucntions of excel if you put in macro that it take 1st 8 didgits from the barcode means left 1st 8 digits and then take product name & its discription from 2 sheet inventry....and if sumting is not in inventry sheet it will hightlight it with diffrent colour and put the qty as it is working but with difrent colour.i will paste 8 digit barcode which is already i have and its product name and discription in invertry sheet.but i want marco like that when i scan barcode it take only 1st 8 digits from the left and take the product name discription and put the qty as many time i will scan the same product.

thank you really vry much but plz help me out in this too..

Barcode ProductName Discription Qty*in this patten