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?

 

[Legacy 356947]

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?




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

Windows 8.1 Excel 2016 PC Thanks!!!

 

[hiker95]

nickchin28,

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



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

 

[marclarzen]

Hi hiker,

for #10 reply. can you show us the solution if we intend to store the information from barcode in another worksheet?

thanks.

 

[hiker95]

Hi hiker,

for #10 reply. can you show us the solution if we intend to store the information from barcode in another worksheet?

thanks.

marclarzen,

Thanks for your new request.

Let's continue at your new thread:

http://www.mrexcel.com/forum/excel-...el-date-time-stamp-out-another-worksheet.html

 

[marclarzen]

marclarzen,

Thanks for your new request.

Let's continue at your new thread:

http://www.mrexcel.com/forum/excel-...el-date-time-stamp-out-another-worksheet.html

Thanks, Hiker. hope you could help me solving this.

 

[hiker95]

marclarzen,

Please see your new thread:

http://www.mrexcel.com/forum/excel-q...worksheet.html

 

[Kadam18]

Hi There, without hijacking the thread, I want exactly what you have created but with a third collumn that give the difference between the times in and times out. I intend to use this to measure times taken in an experiment. This would need to repeat over many columns and rows.

using a PC Windows 7 and Excel 2016

Thanks in advance

 

[hiker95]

Hi There, without hijacking the thread, I want exactly what you have created but with a third collumn that give the difference between the times in and times out. I intend to use this to measure times taken in an experiment. This would need to repeat over many columns and rows.

using a PC Windows 7 and Excel 2016

Thanks in advance

Kadam18,

Welcome to the MrExcel forum.


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, of the raw data, and, what the results should look like.

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


Or, 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

 

[Kadam18]

Kadam18,

Welcome to the MrExcel forum.


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, of the raw data, and, what the results should look like.

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


Or, 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

Hi there, thanks for the reply.

I would like to be able to scan people out and back in again and I would like the spreadsheet to show how long the person was out for, as well as the time out and the time in again. as per the spreadsheet below. I am currently using the following code that you provided much earlier...

Private Sub Worksheet_Change(ByVal Target As Range)' hiker95, 12/18/2012
' http://www.mrexcel.com/forum/excel-questions/672492-scan-barcode-excel-date-time-stamp-out.html
If Intersect(Target, Range("A2:A3000")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
Dim lc As Long, fr As Long, n As Long, nr As Long
With Application
.EnableEvents = False
.ScreenUpdating = False
n = Application.CountIf(Columns(1), Cells(Target.Row, 1))
If n = 1 Then
lc = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
If lc = 1 Then
Cells(Target.Row, lc + 2) = Format(Now, "dd.mm.yyyy h:mm:ss")
ElseIf lc > 2 Then
Cells(Target.Row, lc + 1) = Format(Now, "dd.mm.yyyy h:mm:ss")
End If
Else
fr = 0
On Error Resume Next
fr = Application.Match(Cells(Target.Row, 1), Columns(1), 0)
On Error GoTo 0
If fr > 0 Then
lc = Cells(fr, Columns.Count).End(xlToLeft).Column
If lc = 1 Then
Cells(fr, lc + 2) = Format(Now, "dd.mm.yyyy h:mm:ss")
ElseIf lc > 2 Then
Cells(fr, lc + 1) = Format(Now, "dd.mm.yyyy h:mm:ss")
End If
Target.ClearContents
End If
End If
On Error Resume Next
Me.Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
nr = Me.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Me.Cells(nr, 1).Select
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub


Spreadsheet example

[TABLE="width: 948"]
<colgroup><col span="2"><col span="6"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD] [/TD]
[TD]Time Out[/TD]
[TD]Time In[/TD]
[TD]Time (hh:mm:ss)[/TD]
[TD]Time Out[/TD]
[TD]Time In[/TD]
[TD]Time (hh:mm:ss)[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD] [/TD]
[TD]08.11.2016 15:10:03[/TD]
[TD]08.11.2016 15:10:04[/TD]
[TD] [/TD]
[TD]08.11.2016 15:10:05[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Andy[/TD]
[TD] [/TD]
[TD]08.11.2016 15:10:24[/TD]
[TD]08.11.2016 15:10:51[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD] [/TD]
[TD]08.11.2016 15:10:35[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD] [/TD]
[TD]08.11.2016 15:10:40[/TD]
[TD]08.11.2016 15:10:42[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD] [/TD]
[TD]08.11.2016 15:10:47[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[hiker95]

Kadam18,

The only way to do what you are requesting is to have the worksheet setup something like the following:

Excel 2007
	A	B	C	D	E	F	G	H
1	Name
2
3
4	Name	Time Out	Time In	Time (hh:mm:ss)	Time Out	Time In	Time (hh:mm:ss)
5	James	11.08.2016 14:39:50	11.08.2016 14:43:59	00:04:09	11.08.2016 14:46:01	11.08.2016 14:47:09	00:01:09
6	Andy	11.08.2016 15:03:04	11.08.2016 15:05:15	00:01:49
7
Sheet1

You will only be able to type in/scan a name in cell A2 (yellow cell).

The Worksheet_Change event will find the next blank cell in column A, below the new titles, beginning in cell A5, add the new name and time.

Then cell A2 will be cleared.

Is this acceptable to you?