Excel Barcode Scanning Macro

问题:

I deal with a large number of unique products (10,000+ per year on my own) and I am looking to optimise Excel to make my job more efficient. I have a USB barcode scanner and want to implement this into my process.

My suppliers send me a large spreadsheet with product info, such as barcode number and other product details. I also recieve samples of the product and need to register them within the system, and cross reference them with the spreadsheet.

I am looking for a macro which: when it recieves an input from the scanner, searches and navigates within the spreadsheet to the row where the product is, and if the same barcode has a second scan within 3-5 seconds, fills in the date in column x, and if column x is an occupied cell, fills in the date in column y.

This will allow me to both search for the product without filling in the date (not double scanning) for general lookups, but also to check the product in and out with the date.

Thank you so much in advance for your help!

回答1:

If it helps, the fact that this question relates to barcode scanning could be disregarded - barcode scanners are implemented simply as keyboard devices that 'type' the number scanned (some append 'enter' on the end, some don't, and some are configurable).

So the answer to your question need not be in any way specific to the scanning of barcodes. It pretty much just needs an InputBox that takes a number and then uses that number in the MATCH function.

e.g.

code = InputBox("Please scan a barcode and hit enter if you need to")
matchedCell = match(code, productRange, 0)
matchedCell.Offset(0,2) = Now

where productRange is the range of cells that your product listing takes up.

edit:

To start with, follow a tutorial like this one: http://www.excel-easy.com/vba/create-a-macro.html

You should end up with a button on your form that, when clicked, will put the word "hello" in cell A1.

Once you have this working, replace the 'hello' code with the lines I suggested above and you should be well on your way.