In this video I am going to show you how to scan barodes by category
in Excel so instead of one spot you must select the
category to scan
with this sheet I have products that this rental company rents.
There have more than one of each item and they are identified by serial numbers
So, When you receive items ,you would select the product category and then select the scan
in cell and scan in the serial number.
So I am going to add a few serial numbers to a couple of categories.
I will enter 8888 and 9999 The serial number is added to the category
and changes to green.
meaning that they are available to be rented
So now let us select this other category and enter aaaa and bbbb
lets now select the out tab
on this sheet we only have one box for you to scan in
so let us select the box and scan in a serial number 8888
you will see that it selects the correct category and puts the serial number in a turns red.
now lets go back and look at the In sheet.
The serial number under the cateory has turned red to indicate that is rented.
Let us try to scan in 8888 again it give you a message that this item is not
in stock or it has been rented
Let us go to the stock sheet.
This sheet shows you at any time how many are available.
If you find the category you can see that we have one remaining.
this is a list of what is available in your rental fleet.
let us go back to the In sheet Let us try to enter 9999 again
The message says that this item has not been rented so it cannot be scanned in again
let us try to enter 8888 again and we know that it has been rented.
It asks you whether you want to add it again and you say yes.
You will notice that it has been entered with a yellow to indicate that this has been rented
and might need repair or maintenance.
So now let us look at the code.
first we will look at the code for the IN sheet.
You will notice that if you enter a value anywhere in this row then the Add macro will
be called.
So let us look at Add
I have declared a bunch of values
I have set the scan in cell as prodrng if prodrng is scan here then exit
so the col is the prodrng col and the category is the top row of the column.
the barcode is what ever is in you scanned value
so then we are going to search the column to see if the serial number already exists.
if it doesnt then we will enter it in the lastrow in this column and turn it green
if it does then we are going to count how many times it exists and compare it to how
many times it appears on the out sheet.
if they are the same you send the message asking whether they would like to add it again
or you send a message that this product has not be rented
so now lets look at the sell code
if the scan in is empty then it is going to exit
it is going back to the In sheet and looking for the serial number
if not found then message box not found will be displayed
if it finds the number it is going to look for the number without a red background
if not found it will send a message not in stock
if one if found then it is going to get the category from sheet1 and find the category
on the out sheet.
it will find the last row of the column
then it is going to check if it has been scanned before and ask if it should be scanned again.
it is going to add it to out and turn it to red on In sheet
This is a much longer video than I normal do.
I will include all the code in the descripton of the video and I have a lot of comments
to guide you through if you get stuck
Please subscribe.