Need Help with an Attendance Sheet Using a Barcode Scanner

[AyaAcademy]

I hope someone can assist. I created an Excel spreadsheet using data from Access. I eliminated the columns that we did not need (i.e. email addresses) and attempted to create a formula...I am not good at this...I tried to tell (A1) If (Student ID) True NOW(). This was an epic failure. I read through a very helpful thread on this site: http://www.mrexcel.com/forum/excel-questions/672492-scan-barcode-excel-date-time-stamp-out.html. But wasn't able to tackle 2 issues: 1. Populating the date/time into the next check in field associated with the barcode and (2) how can I refresh the Excel spreadsheet with new names if I imported from Access (Will I have to manually input additional students going forward?)Here is a screenshot: Last Name First Name E-mail Address Student ID Level Room Check In Check Out Check In 2 Check Out 2 Check In 3 Check Out 3 Rohan 1373 Hi Tech Kenneth 1216 Kindergarten Jr Wade 1251 Hi Tech William 1217 Kindergarten Jr Jenna 1218 4th Grade Hi Tech Bryce 1450 Hi Tech Katie 1221 Jr

 

[AyaAcademy]

I would like date in words and numerals (May 28, 2013) and time with AM and PM (4:12 PM).

 

[hiker95]

AyaAcademy,

Please play with this before you go live in case we have to change something.

Cell A1 must be the active cell, Yellow background, each time your open the workbook.

Sample worksheet Sheet1 to start:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1
2	Last Name	First Name	E-mail Address	Student ID	Level	Room	Special Circumstances	Check In	Check Out	Check In 2	Check Out 2
3		Rohan		1373		Hi Tech	YTLC
4		Kenneth		1216	Kindergarten	Jr	PnP
5		Wade		1251		Hi Tech	YTLC
6		William		1217	Kindergarten	Jr	YTLC
7		Jenna		1218	4th Grade	Hi Tech	PnP
8		Bryce		1450		Hi Tech	YTLC
9		Katie		1221		Jr	PnP
10
11
Sheet1

You scan in ID number 1234 into cell A1 (and, 1234 is not in the list) (and you will get this):

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1
2	Last Name	First Name	E-mail Address	Student ID	Level	Room	Special Circumstances	Check In	Check Out	Check In 2	Check Out 2
3		Rohan		1373		Hi Tech	YTLC
4		Kenneth		1216	Kindergarten	Jr	PnP
5		Wade		1251		Hi Tech	YTLC
6		William		1217	Kindergarten	Jr	YTLC
7		Jenna		1218	4th Grade	Hi Tech	PnP
8		Bryce		1450		Hi Tech	YTLC
9		Katie		1221		Jr	PnP
10	???	???	???	1234	???	???	???	May 27,2013 7:49 PM
11
Sheet1

You scan in ID 1450, and, then you scan it in again:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1
2	Last Name	First Name	E-mail Address	Student ID	Level	Room	Special Circumstances	Check In	Check Out	Check In 2	Check Out 2
3		Rohan		1373		Hi Tech	YTLC
4		Kenneth		1216	Kindergarten	Jr	PnP
5		Wade		1251		Hi Tech	YTLC
6		William		1217	Kindergarten	Jr	YTLC
7		Jenna		1218	4th Grade	Hi Tech	PnP
8		Bryce		1450		Hi Tech	YTLC	May 27,2013 7:50 PM	May 27,2013 7:51 PM
9		Katie		1221		Jr	PnP
10	???	???	???	1234	???	???	???	May 27,2013 7:49 PM
11
Sheet1

Then you scan in two more ID and we get this:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1
2	Last Name	First Name	E-mail Address	Student ID	Level	Room	Special Circumstances	Check In	Check Out	Check In 2	Check Out 2
3		Rohan		1373		Hi Tech	YTLC
4		Kenneth		1216	Kindergarten	Jr	PnP	May 27,2013 7:53 PM
5		Wade		1251		Hi Tech	YTLC
6		William		1217	Kindergarten	Jr	YTLC	May 27,2013 7:53 PM
7		Jenna		1218	4th Grade	Hi Tech	PnP
8		Bryce		1450		Hi Tech	YTLC	May 27,2013 7:50 PM	May 27,2013 7:51 PM
9		Katie		1221		Jr	PnP
10	???	???	???	1234	???	???	???	May 27,2013 7:49 PM
11
Sheet1

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).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 05/27/2013
' http://www.mrexcel.com/forum/excel-questions/672492-scan-barcode-excel-date-time-stamp-out.html
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
Dim lr As Long, lc As Long, nc As Long, fr As Long, n As Long, nr As Long
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  n = Application.CountIf(Columns(4), Range("A1"))
  If n = 0 Then
    nr = Range("D" & Rows.Count).End(xlUp).Offset(1).Row
    Range("D" & nr) = Range("A1")
    Range("A1").ClearContents
    Cells(nr, 8) = Format(Now, "mmm dd,yyyy h:mm AM/PM")
    Columns(8).AutoFit
    Cells(nr, 1).Resize(, 3) = "???"
    Cells(nr, 5).Resize(, 3) = "???"
  ElseIf n > 0 Then
    fr = 0
    On Error Resume Next
    fr = Application.Match(Range("A1"), Columns(4), 0)
    On Error GoTo 0
    lc = Cells(fr, Columns.Count).End(xlToLeft).Column
    If lc < 8 Then
      Cells(fr, 8) = Format(Now, "mmm dd,yyyy h:mm AM/PM")
      Columns(8).AutoFit
    ElseIf lc > 7 Then
      Cells(fr, lc + 1) = Format(Now, "mmm dd,yyyy h:mm AM/PM")
      Columns(lc + 1).AutoFit
    End If
  End If
  With Range("A1")
    .ClearContents
    .Select
  End With
  .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

Cell A1 must be the active cell to begin with. Then, each time the Worksheet_Change Event is triggered, the Student ID will be scanned in, and, the date and time stamp will be added to the next available column in the ID's row, and, then cell A1 will be cleared and selected, for the next scan.

 

[AyaAcademy]

Yikes, I just tried it out and received a pop-up box error and the highlighted code was: lc = Cells(fr, Columns.Count).End(xlToLeft).Column

 

[hiker95]

AyaAcademy,

The macro worked correctly on your posted raw data (per my above screenshots).

So that we can get it right this next time:

I would like to see the actual workbook where the error occurred.

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[arrowsecurity]

How would I make this work without having Columns A,B,C,E,F and G

Basically all we need is the scanned info with in and outs.