|
附件是我们单位考勤机的记录,我们的考勤规则是,早晨8:00前签到及9:00后签退算是有效出勤。如何用宏统计出每个人的有效出勤次数。万分感谢!以下是我写的,没能实现!
Sub Macro1()
Dim row_number, column_number, Insert_Column, Work_Name, Work_Number As Integer
Dim morning, night As String
row_number = Range("a65536").End(xlUp).Row
column_number = Range("IV1").End(xlToLeft).Column
Insert_Column = 10 '初始化插入列数
For i = 1 To 2
For k = 1 To 2
If Cells(1, k + 2) = i Then
For s = 1 To Insert_Column
Columns(k + 3).Insert Shift:=xlShiftToRight '插入1列
Next s
Columns(k + 2).Select
Selection.TextToColumns Destination:=Cells(1, k + 2), DataType:=xlDelimited, _
TextQualifier:=xlNone, Space:=True, OtherChar:=" ", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True
Cells.Select
Selection.Replace What:="AM", Replacement:=""
Selection.Replace What:="PM", Replacement:=""
For n = 2 To row_number
For m = 1 To Insert_Column '判断有没有8点前签到
If Hour(Cells(n, m + 2).Value) < 8 And Cells(n, m + 2).Value <> "" Then
morning = "Yes"
Else: morning = ""
End If
Next m
For m = 1 To Insert_Column '判断有没有9点后签到
If Hour(Cells(n, m + 2).Value) >= 9 And Cells(n, m + 2).Value <> "" Then
night = "Yes"
Else: night = ""
End If
Next m
Cells(1, 1) = Hour(Cells(n, m + 2).Value)
If morning = "Yes" And night = "Yes" Then
Cells(n, k + 2) = "是"
Else: Cells(n, k + 2) = ""
End If
Next n
End If
For s = 1 To Insert_Column
Columns(k + 3).Delete
Next s
Next k
Next i
End Sub
- Sub tt()
- t1 = TimeValue("8:00")
- t2 = TimeValue("9:00")
- arr = Sheet1.[a1].CurrentRegion.Offset(1)
- ReDim brr(1 To UBound(arr), 1 To 3)
- For i = 1 To UBound(arr)
- brr(i, 1) = arr(i, 1)
- brr(i, 2) = arr(i, 2)
- For j = 3 To UBound(arr, 2)
- x = Trim(arr(i, j))
- If InStr(x, " ") > 0 Then
- xrr = Split(x, " ")
- xfirst = TimeValue(xrr(0))
- xlast = TimeValue(xrr(UBound(xrr)))
- If xfirst <= t1 And xlast >= t2 Then brr(i, 3) = brr(i, 3) + 1
- End If
- Next
- Next
- With Sheet2
- .[a1].Resize(1, 3) = Array("工号", "姓名", "有效出勤")
- .[a2].Resize(i - 1, 3) = brr
- End With
- End Sub
复制代码
|
|