本帖最后由 mjzxlmg 于 2013-3-8 21:54 编辑
搞不明白,早退是如何判断的
异常表的开始、结束时间等不清楚是如何得来的
[code=vb]Sub test() '引用 Microsoft Scripting Runtime
Dim dic As New Dictionary, arr, brr(), i&, j&, m&, s&, crr(), n&, drr, col&
Sheet1.Activate
arr = [a1].CurrentRegion.Value
col = Val(ActiveSheet.Name) + 1
ReDim brr(1 To UBound(arr), 1 To 7)
ReDim crr(1 To UBound(arr), 1 To 6)
ReDim drr(1 To UBound(arr), 1 To 1)
For i = 2 To UBound(arr)
s = dic(arr(i, 1))
If s = Empty Then
m = m + 1
dic(arr(i, 1)) = m
s = m
brr(s, 1) = arr(i, 1)
End If
brr(s, 2) = brr(s, 2) + Val(arr(i, 6))
brr(s, 3) = brr(s, 3) + Val(arr(i, 7))
If Len(arr(i, 8)) Then brr(s, 4) = brr(s, 4) + 1
brr(s, 6) = brr(s, 6) + Val(arr(i, 9))
brr(s, 7) = brr(s, 7) + Val(arr(i, 10))
drr(s, 1) = brr(s, 7)
If Len(arr(i, 10)) Then
n = n + 1
crr(n, 1) = arr(i, 2)
crr(n, 2) = arr(i, 1)
If arr(i, 4) = "" And arr(i, 5) = "" Then
crr(n, 3) = "8:30"
crr(n, 4) = "18:00"
ElseIf Len(arr(i, 4)) And arr(i, 5) = "" Then
'crr(n, 3) = "8:30"
'crr(n, 4) = "12:00"
ElseIf Len(arr(i, 5)) And arr(i, 4) = "" Then
crr(n, 3) = "8:30"
crr(n, 4) = "12:00"
Else
crr(n, 3) = arr(i, 4)
crr(n, 4) = arr(i, 5)
End If
crr(n, 5) = "年假"
crr(n, 6) = arr(i, 10)
End If
Next
Sheet2.[a5].Resize(m, 7).Value = brr
Sheet3.[a5].Resize(n, 6).Value = crr
Sheet4.Cells(5, col).Resize(m).Value = drr
Set dic = Nothing
Erase arr: Erase brr: Erase crr: Erase drr
End Sub[/code]
|