To keep your employee punctual Biometric Fingerprints Reader attendance system is one of most successful way of attendance management. In all management roles and cases, MS Excel has proposed vibrate solutions which turn the task into formal and more production of a sequel.

Attendance Management Fingerprints Reader

In most cases, whether educational institutes, corporate offices, factories, large industries and in short any project, employee presence and proper track of their attendance is the most concerned department.

How Biometric Fingerprints Reader Works:

For all project or organizational administration, it is the sum worthy job ensures the discipline by making everyone punctual and regular on the duty.

Sub HighlightProblems()
Dim wsRawData As Worksheet
Dim wsSAPIds As Worksheet
Dim rngColHead As Range
Dim lngLastCol As Long
Dim lngLastRow As Long
Dim rngSAP_ID As Range
Dim rngCel As Range
Dim rngRawData As Range

Set wsRawData = Worksheets(“Sheet1″)

With wsRawData
.Select
‘Confirm if Total Events on last row and if so, delete the row.
Set rngCel = .Cells.Find(What:=”total events:”, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rngCel Is Nothing Then
rngCel.EntireRow.Delete
End If

‘Find last used column
lngLastCol = LastColOrRow(.Cells, 2)  ‘2nd parameter: Use 1 for row or 2 for column
If lngLastCol = 0 Then
MsgBox “No data on ” & wsRawData.Name & “.” & vbCrLf & _
“Therefore no last used column or row.” & vbCrLf & _
“Processing terminated.”
Exit Sub
End If

‘Find last used row
lngLastRow = LastColOrRow(.Cells, 1)  ‘2nd parameter: Use 1 for row or 2 for column

‘Test if all headers present and if not, insert dummy header/s
Set rngColHead = .Range(.Cells(1, 1), .Cells(1, lngLastCol))
For Each rngCel In rngColHead
If Trim(rngCel.Value) = “” Then
rngCel.Value = “Col_” & rngCel.Column
End If
Next rngCel

‘Assign full range of data to a range variable
Set rngRawData = .Range(.Cells(1, 1), .Cells(lngLastRow, lngLastCol))

‘Sort data: 1st Key SAP Id, 2nd Key Badge Id, 3rd Key Date/Time
rngRawData.Sort Key1:=Range(“B2”), Order1:=xlAscending, _
Key2:=Range(“A2”), Order2:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

‘Key2:=Range(“C2″), Order2:=xlAscending, _

‘Re-assign data without column headers for Conditional Format
With rngRawData
Set rngRawData = rngRawData.Offset(1, 0).Resize(.Rows.Count – 1, .Columns.Count)
End With

‘Conditional format to identify Adjacent IN and/or Adjacent OUT
rngRawData.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:=”=OR($H2=OFFSET($H2,1,0),$H2=OFFSET($H2,-1,0))”
Selection.FormatConditions(1).Interior.ColorIndex = 27

.Range(“A1″).Select
End With

End Sub

Function LastColOrRow(rngCells As Range, lngSearchOrder As Long) As Long
‘Positive method of finding last used Column or Row on a worksheet
Dim rng As Range

Set rng = rngCells.Find(What:=”*”, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=lngSearchOrder, _
SearchDirection:=xlPrevious, _
MatchCase:=False)

If Not rng Is Nothing Then
If lngSearchOrder = 1 Then
LastColOrRow = rng.Row
Else
LastColOrRow = rng.Column
End If
Else
LastColOrRow = 0
End If

End Function

Since told already, attendance management is one critical portion of work which requires daily updating manuals and attendance reports. For this very purpose, both hardware and software advancements have driven a fast track solution which is biometric recognition system.

By implementing an auto-filling and marking code of VBA being casted for this very purpose, attendance can be recorded more easily in quite a lesser time and with least concentration. Using a biometric scanning device being integrated with MS Excel spreadsheet based attendance note, one can easily keep a track of daily employee attendance their punctuality and deployment countdown time for session that helps in performance improvement.

All you need to be stock a device find or create a good working VBA code which instruct your attendance report spreadsheet to mark subjected names against their relevant finger prints match or face detection. This technique will quickly fill up the attendance section and employee or supervisor doesn’t need to fill the box manually. It also avoids any false entry as no block will be marked unless the real record matches.

Comments