楼主没有说明哪些部门是后勤部门,也没有说明哪些部门上中班和夜班.
按照自己的思路,把考勤的格式转换成了2维表.
剩下的由楼主自己去整理.
代码供参考
- Sub 转换考勤格式()
- Dim Arr, Brr
- Arr = Sheet1.UsedRange
- ReDim Brr(1 To UBound(Arr) / 2 * UBound(Arr, 2), 1 To 7)
- Brr(1, 1) = "日期": Brr(1, 2) = "工号": Brr(1, 3) = "姓名": Brr(1, 4) = "部门": Brr(1, 5) = "上班": Brr(1, 6) = "下班": Brr(1, 7) = "部门性质"
- k = 2
- For i = 2 To UBound(Arr)
- If Arr(i, 1) = "工 号:" And Arr(i, 9) = "姓 名:" And Arr(i, 19) = "部 门:" Then
- s1 = Arr(i, 3)
- s2 = Arr(i, 11)
- s3 = Arr(i, 21)
- End If
- For j = 1 To UBound(Arr, 2)
- Brr(k, 1) = j
- Brr(k, 2) = s1
- Brr(k, 3) = s2
- Brr(k, 4) = s3
- Select Case s3
- Case "行政人事部", "卫生", "食堂", "安保", "供应部", "职能室", "维修工段"
- Brr(k, 7) = "后勤部门"
- Case Else
- Brr(k, 7) = "非后勤部门"
- End Select
- If (Arr(i + 1, j)) <> "" Then
- If Split(Arr(i + 1, j), Chr(10))(0) < "12:00:00" Then
- Brr(k, 5) = Split(Arr(i + 1, j), Chr(10))(0)
- Else
- Brr(k, 5) = 0
- Brr(k, 6) = Split(Arr(i + 1, j), Chr(10))(0)
- GoTo 100
- End If
- If (UBound(Split(WorksheetFunction.Replace(Arr(i + 1, j), Len(Arr(i + 1, j)), 1, ""), Chr(10)))) > 0 Then
- Brr(k, 6) = Split(WorksheetFunction.Replace(Arr(i + 1, j), Len(Arr(i + 1, j)), 1, ""), Chr(10))(UBound(Split(WorksheetFunction.Replace(Arr(i + 1, j), Len(Arr(i + 1, j)), 1, ""), Chr(10))))
- Else
- Brr(k, 6) = 0
- End If
- Else
- Brr(k, 5) = 0
- Brr(k, 6) = 0
- End If
- 100: k = k + 1
- Next
- i = i + 1
- Next
- Sheet2.[a1].Resize(UBound(Brr), UBound(Brr, 2)) = Brr
- End Sub
复制代码 |