pulsarkuant
New Member
- Joined
- Jan 9, 2011
- Messages
- 20
We are using the attached excel to log Staff START and end times. We have logs from the card readers and basically copying the raw logs to the "Raw Data" sheet and hitting the CommadnButton to distribute to the staff listed in the "Personel" sheet. It works fine, but I want to only pick the very FIRST and the LAST reading during each day and ignore any reading in between. How can I tell the below code to pick the first and the last reading?
Command button on the "Raw Data" sheet has the bleow VB code and attached an image.
Command button on the "Raw Data" sheet has the bleow VB code and attached an image.
VBA Code:
Private Sub CommandButton1_Click()
Dim irow As Long
Application.ScreenUpdating = False
With ActiveSheet
For irow = .Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If UCase(.Cells(irow, 2)) Like "*ADMINDOOR*" Or _
UCase(.Cells(irow, 2)) Like "*LIFT*" Or _
UCase(.Cells(irow, 2)) Like "*CANTEEN*" Or _
UCase(.Cells(irow, 2)) Like "*GATE*" Then
.Rows(irow).Delete
End If
Next
End With
Application.ScreenUpdating = True
For a = 3 To Sheets("Personel").Cells(65000, 1).End(xlUp).Row
For b = 1 To Sheets("Raw data").Cells(65000, 1).End(xlUp).Row
c = Len(Sheets("Personel").Cells(a, 1))
If Left(Cells(b, 2), c) = Sheets("Personel").Cells(a, 1) Then
d = Day(Cells(b, 1)) * 2
If Hour(Cells(b, 1)) > 11 Then d = d + 1
Sheets("Personel").Cells(a, d + 1) = Hour(Cells(b, 1)) & ":" & Minute(Cells(b, 1))
End If
Next
Next
End Sub