easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner ?

**nickyvaux** · 08-01-2015, 06:34 AM

Hi,

I am a novice at macros. By which I mean I have never written one, and have only just started using other people's. I am finding this hard....
We sell wholesale, and receive orders via .csv files. We need to check them off and currently it is manual, which causes errors. We want to check off an item by scanning its barcode from an order sheet. So this is what we need:

We need a sheet that will enable us to enter our orders from our wholesale customers, with up to 11000 items on. It will then enable us to tick the items of the list, by scanning the product barcode from the item straight into the sheet. I can make one like this myself up to this point based on other's VBA (based on this thread http://www.mrexcel.com/forum/excel-q...e-scanner.html)

However... it will then knock 1 off the quantity on the order. When there are none of that item remaining on that order it will produce an error code and beep, so that we know that item is incorrect and not to pack it with the same order. Likewise when an incorrect item is about to be packed, it should error and say the item is incorrect. It will pref show us what is remaining to pack from the order, by greying out completed lines.

It will also enable us to manual override the pick list to cancel an item we cannot fulfil if it is out of stock.

Ideally, but not necessarily, it will also tell us how many boxes of 4 items per box we have just packed.

I have attached my best effort based on the vba used in this thread:

http://www.excelforum.com/l/759205-a...ode-input.html

It is nearly there, but I am getting random 'code not found' messages against some EANs (try it with PB-27-Green-Black, ean 0799456401034 for example), which would completely screw the whole system up. I have used the column formatted as general, and as text, and as number, but cannot stop it from happening.

Alongside this, as you can see from the instructions sheet, I am going to have to ask the user to bring in the information from an .xls file which has the columns and headers formatted differently. As the scanner person isn't an excel user, i would pref like to be able to set up a macro to auto copy the imported information from sheet 3, or wherever, to sheet 1, using the relevant columns transferred to the correct places on sheet 1, and not including the headers where they shouldn't be included.

Is there anyone please, please, please that can help me? I have spent too long trying now and my brain is frazzled....

Cheers,

Nicky

INCREDIBLY IMPORTANT EXCEL SHEET v2 trial.xls

**event21** · 08-01-2015, 07:13 AM

Hi -

Welcome to the forum.

on a quick look, this line causing the not found in your particular sample

Please Login or Register  to view this content.

do a manual find of that ean 0799456401034 and you can see 2 results

event

**nickyvaux** · 08-01-2015, 07:19 AM

thanks for locating the broken bit, do you know how i should fix it?

**event21** · 08-01-2015, 07:21 AM

Hi -

Please Login or Register  to view this content.

event

**event21** · 08-01-2015, 07:22 AM

Mods - redundant post due to error.

Hi -

Please Login or Register  to view this content.

event

**nickyvaux** · 08-01-2015, 07:28 AM

Hi have just realised that this is because of multiples of that ean featuring, is that correct? The scan needs to work even with multiples as we receive orders with multiples of the same ean in them, stupidly! What can I change it to to correct this?

**event21** · 08-01-2015, 07:31 AM

Hi -

See post #4

event

**nickyvaux** · 08-01-2015, 07:34 AM

Thanks v much will try it shortly.

**nickyvaux** · 08-01-2015, 07:38 AM

Anyone out there got any advice on the second macro at all? Tried to record it myself but it didn't work.

**mehmetcik** · 08-01-2015, 07:56 AM

Hi Nicky.

You have a userform that you use to search a list of code numbers. That part is working.
Adjacent to the code number is the item count

You usecount if to deal with cases where the code wasn't found.

If the item count > 1 then you want that count reduced by 1
if 0 you want a message error

Ok.

Your problem is that countif is not working for your data.

You have leading zeros, and excel is struggling with them

I tried using Match instead and that works

Please Login or Register  to view this content.

**mehmetcik** · 08-01-2015, 07:59 AM

I would remove all items with 0 in column C to another sheet.

Or possibly to the bottom of this sheet.

Change your Macro Scan to :

Please Login or Register  to view this content.

**event21** · 08-01-2015, 08:22 AM

Hi -

Why not uploading sample of csv file? Maybe there is a way to directly dumping it into your sheet1 instead of getting the data from sheet3 or something.

just a suggestion, sounds good isn't it?

event

**nickyvaux** · 08-01-2015, 08:36 AM

Hi, have tried match code below. it is giving me 'invalid use of me.code' as a result when i debug. not knowing what i am doing, i don't know what this means.

Have also tried to change original code to

If WorksheetFunction.CountIf(Sheets("Sheet1").Columns(2), TextBox1.Value) >0 Then

but cannot find how to do this. on entering the original vba, i see:

Sub Scan()
UserForm1.Show
End Sub

when i downloaded the original the code was visible?

**event21** · 08-01-2015, 09:14 AM

Hi -

From the left side, you can see the Project Explorer, double click on the userform and when the form open double click on the button, then you can see the codes of remove button. if the project explorer is not showing, click Ctrl + r while your in the window of sub scan() code.

event

**nickyvaux** · 08-01-2015, 10:32 AM

That worked! Thanks! have edited original code with
If WorksheetFunction.CountIf(Sheets("Sheet1").Columns(2), TextBox1.Value) >0 Then
and all eans seem to work so far.

More functionality required if there are any ideas out there:

1.Macro to populate sheet 1 product name, code and count from csv file with data in it with wrong headers. maybe via an import button. currently thinking could paste csv order file as 1st sheet and use vlookup formulas but am convinced that the vlookup formula in product code would mean the scanner is unable to read the value over the formula- is that correct? also would i then have to manually resort the csv file by ascending product code order for it to work?

INCREDIBLY IMPORTANT EXCEL SHEET v2 trial.xls Pick List Template.1.xlsx

2. would like to include code to take you to the line on the page the barcode has just been scanned from and the count reduced from every time you scan. is there somme kind of goto code i can insert?

3. would like to clear scan screen after every scan in order not to accidentally remove same item without scanning new item in. this is probably v important. scanner user is not an excel person and will be scanning 11000 items...

**nickyvaux** · 08-01-2015, 10:34 AM

Pick List Template.1.xlsx

above is an example .csv file as we use them. if you guys can work out how that can appear magically in my sheet 1, I will be incredibly grateful.

**event21** · 08-02-2015, 03:49 AM

Hi -

Please upload CSV file and not excel file. just upload the original if it has no sensitive information.

event

**nickyvaux** · 08-02-2015, 04:18 AM

file to play with including scan function.xls Pick List Template.1.csv

Hi event21,

So I have added the csv as it is. but i have also added where i am up to. i am not saying it is a neat fix. but i have added three separate macros to import the data from the sheet when it is copied 'as is' from whatever file into sheet 3. as a workaroundits ok and requires less human input.(ie less error)

now i am trying to add functionality to the 'scan' macro. specifically i am trying to add some kind of goto line type function in the middle of the if then code, so that after removing 1 from count, it takes you to the relevant line. if it also makes a sound to register the change, and i can add that, that will be an extra check for the user.

following that i will be trying to freeze the stupid buttons in position so that the rest scrolls without the buttons and instructions scrolling. this doesn't seem to blimming work no matter what i do. otherwise it will take you to the correct line and leave you to scroll back up every time.

**nickyvaux** · 08-02-2015, 04:50 AM

ok. so, macro still not working on data where the same product code features on multiple lines. returns 'code not found' error. needs to work with both multiple counts on each product code and multiple instances of product code on different lines. aaaagrh.

Have realised can use 'while...wend' loop to repeat 'if then else' until target calle value can no longer be found, but don't know how to integrate this into code: current code is:

Private Sub btnAdd_Click()
Dim TargetCell As Range
If WorksheetFunction.CountIf(Sheets("Sheet1").Columns(2), TextBox1.Value) = 1 Then
Set TargetCell = Sheets("Sheet1").Columns(2).Find(TextBox1.Value, , xlValues, xlWhole).Offset(0, 1)
TargetCell.Value = TargetCell.Value + 1
Else
MsgBox "Code not found DO NOT ADD THIS ITEM"
End If
Me.Hide
End Sub

Private Sub btnRemove_Click()
Dim TargetCell As Range

If WorksheetFunction.CountIf(Sheets("Sheet1").Columns(2), TextBox1.Value) > 0 Then
Set TargetCell = Sheets("Sheet1").Columns(2).Find(TextBox1.Value, , xlValues, xlWhole).Offset(0, 1)
If TargetCell.Value >= 1 Then
TargetCell.Value = TargetCell.Value - 1
Beep
Else
MsgBox "Value already zero DO NOT ADD THIS ITEM"
End If
Else
MsgBox "Code not found DO NOT ADD THIS ITEM"
End If
Me.Hide
End Sub

Private Sub UserForm_Click()

End Sub

**event21** · 08-02-2015, 07:45 AM

Hi -

When posting, always remember to put CODE tags...

This is how I do it. I have only 1 sheet.
1. Click in Load PO button
2. Select the PO csv file and double click
3. Done.

try the attached file and let me know.

event

**nickyvaux** · 08-02-2015, 08:07 AM

thats excellent! though it does currently bring the product codes in as rounded up numbers so wont be able to scan from them. or thats something i did wrong, probably? sorry about code pasting :/
Screenshot 2015-08-02 13.04.53.png

**event21** · 08-02-2015, 08:09 AM

Hi -

I have amended the attachment, please download it again.

event

**nickyvaux** · 08-02-2015, 08:30 AM

its still doing it, at least on mine. am i bringing it in wrong?

Screenshot 2015-08-02 13.29.28.png

**event21** · 08-02-2015, 10:48 AM

Hi -

Please download this one. I have changed the file name.
on the same folder of this file, after you clicked in the Load PO button, please check if there is Schema.ini file created and let me know.

event

**nickyvaux** · 08-03-2015, 03:22 AM

Cheers Event, it works great with new unmodified POs, the fault is at my end:
If you open the original customer order CSV to edit it, and then save it as a csv having edited it (we need to conglomerate our order CSVs into warehouse shipments before processing), it saves all product codes as for instance 7.86471E+11. We have discovered this in the past, and no longer save as CSV only as Excel workbook, but I had forgotten this and it had seemed to make more sense to import from CSV. Can I modify the code to import Excel workbooks or will that not work? Do I just change every instance of .csv to .xls?

**event21** · 08-03-2015, 03:32 AM

Hi -

This will work in CSV file 100%, if you save or edit the csv in excel the formatting might lost.

Glad it works.

event

easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner ?

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Re: easy auto macro to delete 1 quantity of an item from an order sheet by barcode scanner

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] barcode scanner

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions