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 hiker95

i wish to help cleaning snow

Thanks your macro it is OK. But still have a problem

According to your latest macro, before scanning the 2nd, 3rd and 4th scans i must select the cell where is the barcode S00125A001.
i will have hunderd barcodes, and it will be hard to find where the S00125A001.
Can you help me the solve the latest problem.
Four scans but just one barcode name (in column A).

 

[hiker95]

Four scans but just one barcode name (in column A).

benkimya,

I think I finally understand what you are now trying to do.

Sample raw data, and, results (see below the screenshot for how the macro works):

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 15:48:57			29.12.15 15:49:05			0.00000	29.12.15 15:49:12			29.12.15 15:49:19
5								0.00000
6								0.00000
7								0.00000
8								0.00000
9								0.00000
10								0.00000
11								0.00000
12								0.00000
13								0.00000
14								0.00000
Sayfa1

1. the first scan is into cell A4, and, the date/time is placed in cell B4

2. the second scan is into cell A6, and, the date/time is placed in cell E4, and, A6 is cleared.

3. the third scan is into cell A9, and, the date/time is placed in cell I4, and, A9 is cleared.

4. the fourth scan is into cell A11, and, the date/time is placed in cell L4, and, A11 is cleared.

5. the last scan is into cell A13, and, NOTHING happens, BUT, A13 is cleared.


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

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 12/29/2015, ME672492
' Multiple same barcode scan in column A
If Intersect(Target, Range("A4:A3000")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
Dim n As Long, a As Range
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  n = Application.CountIf(Columns(1), Target.Value)
  If n = 1 Then
    Cells(Target.Row, 2) = Format(Now, "dd.mm.yy hh:mm:ss")
  ElseIf n > 1 Then
    Set a = Columns(1).Find(Target.Value, LookAt:=xlWhole)
    If Not a Is Nothing Then
      If Cells(a.Row, 5) = vbEmpty Then
        Cells(a.Row, 5) = Format(Now, "dd.mm.yy hh:mm:ss")
      ElseIf Cells(a.Row, 9) = vbEmpty Then
        Cells(a.Row, 9) = Format(Now, "dd.mm.yy hh:mm:ss")
      ElseIf Cells(a.Row, 12) = vbEmpty Then
        Cells(a.Row, 12) = Format(Now, "dd.mm.yy hh:mm:ss")
      End If
      Target.ClearContents
    End If
  End If
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

 

[benkimya]

Thanks Hiker95,
You are Great you solved my problems, thanks so much

 

[hiker95]

benkimya,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[benkimya]

Hi Hiker95,

i need help again, i am so sorry.
my next page (this is final )
https://www.dropbox.com/s/amu7y6pybtx895m/numune kabul.xlsx?dl=0

i want to scan the barcode, and should be placed to Colum B barcodes, and Column C date/time.
just one scan for each barcode, no repetition

i hope you will help me, thanks

 

[hiker95]

benkimya,

Sample raw data, and, results:

Excel 2007
	A	B	C	D	E	F	G
1	Sira No	Barkod	Numune Kabul Tarihi	lm Tarihi	Teslim Eden	Teslim Alan
2	1	S00125A004	30.12.15 15:19:35
3	2
Numune Kabul

Give the following a try:

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 12/30/2015, ME672492
If Intersect(Target, Range("B2:B3000")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Cells(Target.Row, 3) = Format(Now, "dd.mm.yy hh:mm:ss")
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

 

[benkimya]

Hi, thanks it is ok.
But, when i scan the same barcode it stamps in new rows,
i added excel below. i want to scan the same barcode just one time, when i scan second time the same barcode, nothing should be happen

https://www.dropbox.com/s/amu7y6pybtx895m/numune kabul.xlsx?dl=0

 

[hiker95]

i want to scan the barcode, and should be placed to Colum B barcodes, and Column C date/time.
just one scan for each barcode, no repetition

benkimya,

The following macro will delete any new scanned barcode, if the barcode already exists in column B.

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/30/2015, ME672492
' Version 2, if dupicate barcode is scanned then delete the duplicate
If Intersect(Target, Range("B2:B3000")) 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(2), Target.Value)
  If n = 1 Then
    Cells(Target.Row, 3) = Format(Now, "dd.mm.yy hh:mm:ss")
  ElseIf n > 1 Then
    Target.ClearContents
  End If
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

 

[Legacy 356947]

Hi, is there a way for you to display the time in the 12 hour format and not the 24 hour format. Also, is there a way, where you can subtract the 2 times to calculate the amount of time elapsed in between Time In and Time Out. Thanks!!

 

[hiker95]

nickchin28,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

is there a way for you to display the time in the 12 hour format and not the 24 hour format. Also, is there a way, where you can subtract the 2 times to calculate the amount of time elapsed in between Time In and Time Out.

I would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


If you are not able to provide screenshots, then:

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

https://dropbox.com