|
|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub 考勤统计()
Dim ar As Variant
ar = Sheet1.[a1].CurrentRegion
mrr = Array("上午上班", "上午下班", "下午上班", "下午下班", "加班上班", "加班下班")
ReDim br(1 To UBound(ar) * 6, 1 To 6)
For i = 3 To UBound(ar)
If Trim(ar(i, 2)) <> "" Then
rr = Split(ar(i, 5), " ")
For s = UBound(rr) To 0 Step -1
If TimeValue(rr(s)) < TimeValue("12:00:00") Then
sj1 = rr(s)
ElseIf TimeValue(rr(s)) > TimeValue("12:00:00") And TimeValue(rr(s)) < TimeValue("13:00:00") Then
sj2 = rr(s)
ElseIf TimeValue(rr(s)) > TimeValue("12:00:00") And TimeValue(rr(s)) < TimeValue("17:00:00") Then
sj3 = rr(s)
ElseIf TimeValue(rr(s)) > TimeValue("17:00:00") And TimeValue(rr(s)) < TimeValue("18:00:00") Then
sj4 = rr(s)
ElseIf TimeValue(rr(s)) > TimeValue("18:00:00") And TimeValue(rr(s)) < TimeValue("19:00:00") Then
sj5 = rr(s)
ElseIf TimeValue(rr(s)) > TimeValue("19:00:00") Then
sj6 = rr(s)
End If
Next s
n = n + 1
For j = 1 To 4
br(n, j) = ar(i, j)
Next j
br(n, 5) = sj1
br(n, 6) = "上午上班"
n = n + 1
For j = 1 To 4
br(n, j) = ar(i, j)
Next j
br(n, 5) = sj2
br(n, 6) = "上午下班"
n = n + 1
For j = 1 To 4
br(n, j) = ar(i, j)
Next j
br(n, 5) = sj3
br(n, 6) = "下午上班"
n = n + 1
For j = 1 To 4
br(n, j) = ar(i, j)
Next j
br(n, 5) = sj4
br(n, 6) = "下午下班"
n = n + 1
For j = 1 To 4
br(n, j) = ar(i, j)
Next j
br(n, 5) = sj5
br(n, 6) = "加班上班"
n = n + 1
For j = 1 To 4
br(n, j) = ar(i, j)
Next j
br(n, 5) = sj6
br(n, 6) = "加班下班"
End If
Next i
With Sheet2
.[a1].CurrentRegion.Offset(1) = ""
.[a2].Resize(n, 6) = br
End With
End Sub |
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2021-3-14 16:08
楼主