Scan Barcode to excel for 3 times only

[ygurram]

Hi,
I am looking something similar to thread shown below.
https://www.mrexcel.com/forum/excel-questions/672492-scan-barcode-excel-date-time-stamp-out.html.

I am looking for the similar code that hiker has provided in his reply #18 but My way of arranging cells is little different and I need to scan a particular Part number for only 3 times. As you can see below Part number(which needs to be scanned is in D column and cell starts from D3). 1st, 10th & Last are in columns G,H & I( this is where I need time stamps when scanned 3 times)
[TABLE="width: 801"]
<colgroup><col><col span="2"><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]JOB[/TD]
[TD]Does Part Match spec. sheet[/TD]
[TD]Does part # match label?[/TD]
[TD]Part Number[/TD]
[TD]Is PPE required for Part Number?[/TD]
[TD]What is the Country of Origin[/TD]
[TD]1st[/TD]
[TD]10th[/TD]
[TD]Last[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[ygurram]

Hi,
I am looking something similar to thread shown below.
https://www.mrexcel.com/forum/excel-questions/672492-scan-barcode-excel-date-time-stamp-out.html.

I am looking for the similar code that hiker has provided in his reply #18 but My way of arranging cells is little different and I need to scan a particular Part number for only 3 times. As you can see below Part number(which needs to be scanned is in D column and cell starts from D3). 1st, 10th & Last are in columns G,H & I( this is where I need time stamps when scanned 3 times)
[TABLE="width: 801"]
<tbody>[TR]
[TD]JOB[/TD]
[TD]Does Part Match spec. sheet[/TD]
[TD]Does part # match label?[/TD]
[TD]Part Number[/TD]
[TD]Is PPE required for Part Number?[/TD]
[TD]What is the Country of Origin[/TD]
[TD]1st[/TD]
[TD]10th[/TD]
[TD]Last[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Here is the Excel format I am having. Please help
[TABLE="width: 576"]
<colgroup><col width="64" style="width:48pt" span="9"> </colgroup><tbody>[TR]
[TD="width: 576, bgcolor: transparent, colspan: 9"]Heading[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Job[/TD]
[TD="width: 64, bgcolor: transparent"]Does PN match the sheet[/TD]
[TD="width: 64, bgcolor: transparent"]Does PN match the label[/TD]
[TD="width: 64, bgcolor: transparent"]Is PPE required[/TD]
[TD="width: 64, bgcolor: transparent"]what is country of origin [/TD]
[TD="width: 64, bgcolor: transparent"]Part Number[/TD]
[TD="width: 64, bgcolor: transparent"]1st [/TD]
[TD="width: 64, bgcolor: transparent"]10th[/TD]
[TD="width: 64, bgcolor: transparent"]100th[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[ygurram]

[TABLE="width: 576"]
<colgroup><col width="64" style="width:48pt" span="9"> </colgroup><tbody>[TR]
[TD="width: 576, bgcolor: transparent, colspan: 9"]Heading[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Job[/TD]
[TD="width: 64, bgcolor: transparent"]Does PN match the sheet[/TD]
[TD="width: 64, bgcolor: transparent"]Does PN match the label[/TD]
[TD="width: 64, bgcolor: transparent"]Is PPE required[/TD]
[TD="width: 64, bgcolor: transparent"]what is country of origin [/TD]
[TD="width: 64, bgcolor: transparent"]Part Number[/TD]
[TD="width: 64, bgcolor: transparent"]1st [/TD]
[TD="width: 64, bgcolor: transparent"]10th[/TD]
[TD="width: 64, bgcolor: transparent"]100th[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[hiker95]

ygurram has exceeded their stored private messages quota and cannot accept further messages until they clear some space.

ygurram,

No more Private Messages......

I have attempted to reply to you on two occasions, but, you have reached your quota (per the above).

 

[hiker95]

ygurram,

So that I can get it right on the first try, I would like to see your actual raw data workbook/worksheets, with the raw data in one worksheet, and, in another worksheet (manually formatted by you) the results that you are looking for.

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[ygurram]

ygurram,

So that I can get it right on the first try, I would like to see your actual raw data workbook/worksheets, with the raw data in one worksheet, and, in another worksheet (manually formatted by you) the results that you are looking for.

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

Hiker95,
I have attached my workbook in dropbox. Please see below link for excel worked/ I have modified the format again.

https://www.dropbox.com/s/bmk0gh6n3qbu9zf/Book1.xlsx?dl=0

As you can see my C3 will be my active cell for barcode scanning under Part Number. Your code given in reply #18 from below thread worked perfectly fine but I need the barcode to be scanned 3 times only for each part number, in other words I need only 3 time stamps for a particular part number in a row that will be in Column D,E & F. Can you also please add a code for autosave?
https://www.mrexcel.com/forum/excel-questions/672492-scan-barcode-excel-date-time-stamp-out.html

One more thing, Is it possible to avoid Manual entry for Column C,D,E & F. so that data can inputted in column C is through barcode only.

Thanks in Advance!

Ygurram.

 

[hiker95]

I have attached my workbook in dropbox. Please see below link for excel worked/ I have modified the format again.

As you can see my C3 will be my active cell for barcode scanning under Part Number.

ygurram,

That will not work based on the macro you have a link to.

Can we use cell C1 to scan ALL the barcodes into?


If we scan barcode 12345678AA into cell C1, then the new macro will search from C2 down to see if the barcode already exists.

If it does on not exist, then, it will add it to cell C3, and, put the date/time/stamp into cell D3.

Then we clear cell C1.

If we scan barcode 12345678AA into cell C1 again, the macro will search from C2 down, and, it will find the barcode in cell C3, and, then it will check row 3 for the next blank cell, D3, and, put the date/time/stamp into cell E3.


If we scan barcode 12345678BB into cell C1, then the new macro will search from C2 down to see if the barcode already exists.

If it does not exist, then, it will add it to cell C4, and, put the date/time/stamp into cell D4......

Then we clear cell C1, and, wait for the next barcode.


I hope that you can understand the above logic.

If you do, is what I have explained acceptable to you?


If not, then maybe someone else on MrExcel will be able to solve your request.

 

[ygurram]

Good day Hiker,

Sorry for the late response I was busy in figuring out what format I need to use.

I have updated my Macro with final format. Please see below link.
https://www.dropbox.com/s/xrq4xou2xihicir/VALD TRAIL.xlsm?dl=0

Let me explain you better what I am looking for. The idea is to scan a particular part number at least 3 times and to avoid same part number being scanned twice. I have used Data validation with formula =COUNTIF($B$1:$B$100,B1)=1 to address this issue prevent duplicates. If you try entering same Partnumber in column B it will give me alert which is exactly what I am looking for to alert the operators.

Operator selects B4 to scan 12345678AA and I am looking for time stamps in format 12345678AA(part number), HH:MM starting from Column L.

Everytime they need to scan a barcode they would need to select the active cell. Suppose if they want scan the part number 12345678AB they will select B5 and B6 for 12345678AC.


Would it possible to have the time stamp along with part number when scanned?

Thanks in advance!

Yashwanth

 

[hiker95]

ygurram,

I have had problems in the past when attempting to download an Excel file with macros, with the xlsm file extension.

Please remove all macros, and, then rename the workbook using the xlsx file extension, and, then repost on dropbox.


Then in your next reply, also include your macro:

When posting VBA code, please use Code Tags - like this:

[code=rich]

'Paste your code here.

[/code]

 

[ygurram]

Hiker,

Please see below link to download workbook in xlsx format.
https://www.dropbox.com/s/dep44kuaun8jbsx/VALD TRAIL.xlsx?dl=0

I have not used any Macros in this workbook except the formula written in reply#8 in Data validation.

Thanks
ygurram.