Scan barcode to excel with date & time stamp in & out.

[qpywsqp]

Hi all. I have research around the internet and finally found out Macro able to solve my issue. But im totally not a programmer. Hopefully someone can help me on this.

I will scan QR Code with a scanner to cell A1 and wanted cell C1 to have date and time stamp for IN. When Scanner scan the same QR Code, D1 will have date and time stamp for out. If repeat third time scanning the same QR code after a few scanning, it will appear on next row. Which means A8 with the code and C8 with date and time stamp for IN again.

Is that possible to done with Macro?

 

[benkimya]

Hi

Thanks for your help and quick answer.

1. i am using Win7 and office 2013
2. PC

Column A is barcode, B is first time and date column, H is the scond date and time
When i scan barcode, Barcode is written to Column A, and the date and time should be in B, when i scan the same barcode second time the date and time should be in coulmn H.

i have many barcodes, i wanna use this excel for filter follow-up in laboratory. instead of writing barcodes and date and time , i want to follow up this way

i tried to add excel to dropbox
https://www.dropbox.com/s/q1nv570e310lsno/F-119 ISG TARTIM SONUÇLARI FORMU (2).xlsm?dl=0

 

[hiker95]

When i scan barcode, Barcode is written to Column A3, and the date and time should be in B3

when i scan the same barcode second time the date and time should be in column H.

benkimya,

1. In the same row, H3?

2. Or, can you scan the same barcode again in a different column A row?

2a. Then what should happen?

 

[benkimya]

Hi,

i changed your macro a bit and now i reupload the excel.
https://www.dropbox.com/s/q1nv570e310lsno/F-119 ISG TARTIM SONUÇLARI FORMU (2).xlsm?dl=0
i also scanned my barcode in excel to show what happens.
i always select column A while scanning a barcode then scanned it. barcode name is written to column A from row 1 to row2, row3...
but the problem is date and time stamp is written Column O, the 2nd scanned Column P, 3rd Column R ....
i want to stamp the date and time column B and Column H
i hope to tell my problem
Thanks

 

[hiker95]

benkimya,

We seem not to be able to understand each other.

You have not answered my questions?

See if the following will work for you.

I change the date and time format for the following to also show seconds.

If you scan in S00125A001 two times, you will get this. Is the following screenshot correct?

Here are some manual scans, and, results:

Excel 2007
	A	B	C	D	E	F	G	H
1	BARKOD/ FILTRE KODU			ILK TARTIM (g)
2		TIME&Date	1. Tartim	2. Tartim	3. Tartim	ORTALAMA	Tartimi Yapan	TIME&Date
3	S00125A001	12/26/15 17:55:27				#DIV/0!
4	S00125A002	12/26/15 17:55:36				#DIV/0!
5	S00125A003	12/26/15 17:55:45				#DIV/0!
6	S00125A004	12/26/15 17:55:50				#DIV/0!
7	S00125A101	12/26/15 17:58:16				#DIV/0!
8	S00125A001					#DIV/0!		12/26/15 18:03:22
9						#DIV/0!
10						#DIV/0!
Sayfa1

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 12/26/2015, ME672492
If Intersect(Target, Range("A2:A3000")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
Dim n As Long
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  n = Application.CountIf(Columns(1), Cells(Target.Row, 1))
  If n = 1 Then
    If Cells(Target.Row, 2) = vbEmpty Then
      Cells(Target.Row, 2) = Format(Now, "mm/dd/yy h:mm:ss")
    Else
      Cells(Target.Row, 8) = Format(Now, "mm/dd/yy h:mm:ss")
    End If
  ElseIf n > 1 Then
    Cells(Target.Row, 8) = Format(Now, "mm/dd/yy h:mm:ss")
  End If
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

 

[benkimya]

Hi,

Thanks, macro is going better.
i am sorry, i cant tell what i what correctly.
i added your macro my excel and scanned my barcodes, and reupload to dropbox
https://www.dropbox.com/s/q1nv570e310lsno/F-119 ISG TARTIM SONUÇLARI FORMU (2).xlsm?dl=0

the problem is that;
First scan stamp the date and time in correct cell
The second scan add second S00125A001 barcode in column A. i dont want this
When i scan the same barcode secondly. it should be in H column in the same row (when it is scanned firstly)

Ex:
1st Scan: S00125A001 column A row 8, Time/Date Column B row8 , they are in the same row (row 8) . This is OK
2nd Scan: S00125A001 no need new rowin column A, time/date should be in column H row 8,

i mean that in the second scan , i dont want to be shown the same barcode secondly in column A
i added excel what should be
https://www.dropbox.com/s/83kfovwllly73wp/excel shoul be.xlsm?dl=0

 

[hiker95]

1st Scan: S00125A001 column A row 8, Time/Date Column B row8 , they are in the same row (row 8) . This is OK
2nd Scan: S00125A001 no need new rowin column A, time/date should be in column H row 8,

benkimya,

It sounds like, that you will never scan in S00125A001 a second time in another row in column A.

But, you will always scan in the same barcode in the same original cell.

If the above statements are correct, then, try the following:

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 12/27/2015, ME672492
If Intersect(Target, Range("A2:A3000")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  If Cells(Target.Row, 2) = vbEmpty Then
    Cells(Target.Row, 2) = Format(Now, "mm/dd/yy h:mm:ss")
  Else
    Cells(Target.Row, 8) = Format(Now, "mm/dd/yy h:mm:ss")
  End If
  .EnableEvents = True
  .ScreenUpdating = True
End With

 

[benkimya]

Hi,
i solved my problem and added excel below.

https://www.dropbox.com/s/rgrimvv5p5tuyks/excel shoul be - Kopya.xlsm?dl=0

In the first page (ISG Tartım), i have done i want. No Problem
i scanned the barcode two times in different times, they stamp the date and time in correct cells

But now i have a problem in the second page (Page1)
In this page i must scan the same barcode 4 times in different times. 1st and 2nd stamps are OK, but i dont know how to do the 3rd and 4th stamps to the yellow cells (I4 and H4).

 

[hiker95]

Hi,
i solved my problem and added excel below.

https://www.dropbox.com/s/rgrimvv5p5...opya.xlsm?dl=0

In the first page (ISG Tartım), i have done i want. No Problem
i scanned the barcode two times in different times, they stamp the date and time in correct cells

benkimya,

Thanks for the feedback.

You are very welcome. Glad I could help.

 

[hiker95]

But now i have a problem in the second page (Page1)
In this page i must scan the same barcode 4 times in different times. 1st and 2nd stamps are OK, but i dont know how to do the 3rd and 4th stamps to the yellow cells (I4 and H4).

benkimya,

Let me see what I can come up with.

Be back later today, after I clean up the ice and snow from our first snow storm.

 

[hiker95]

benkimya,

Well, we have two inches of ice/snow, and, it is raining.

Sample new raw data, and, results:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L
1	Barkod/ Filtre Kodu	TOZ							KALINTI
2		Ilk Tartim		Tartimi Yapan	Son Tartim		Tartimi Yapan	Fark (g)	Ilk Tartim		Tartimi Yapan	Son Tartim
3		Tarih/ Saat	Miktar (g)		Tarih/ Saat	Miktar (g)			Tarih/ Saat	Miktar (g)		Tarih/ Saat
4	2871	29.12.15 08:57:42			29.12.15 08:57:44				29.12.15 08:57:46			29.12.15 09:02:06
5
Sayfa1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 12/29/2015, ME672492
If Intersect(Target, Range("A4:A3000")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  If Cells(Target.Row, 2) = vbEmpty Then
    Cells(Target.Row, 2) = Format(Now, "dd.mm.yy hh:mm:ss")
  ElseIf Cells(Target.Row, 5) = vbEmpty Then
    Cells(Target.Row, 5) = Format(Now, "dd.mm.yy hh:mm:ss")
  ElseIf Cells(Target.Row, 9) = vbEmpty Then
    Cells(Target.Row, 9) = Format(Now, "dd.mm.yy hh:mm:ss")
  ElseIf Cells(Target.Row, 12) = vbEmpty Then
    Cells(Target.Row, 12) = Format(Now, "dd.mm.yy hh:mm:ss")
  End If
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.