|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- Sub zzz()
- Sheet2.Activate
- Application.ScreenUpdating = False
- ActiveSheet.UsedRange.ClearContents
- Dim d, ds, dz, arr, i&, j&, k&, m&, n&
- Set d = CreateObject("Scripting.Dictionary")
- Set ds = CreateObject("Scripting.Dictionary")
- Set dz = CreateObject("Scripting.Dictionary")
- arr = Sheet1.[a1].CurrentRegion.Value
- n = UBound(arr) * 2
- ReDim brr(1 To n * 2, 1 To 34)
- brr(1, 1) = "考勤号码": brr(1, 2) = "姓名": brr(1, 3) = "项目\日期"
- For j = 1 To 31
- brr(1, j + 3) = j
- Next
- m = 1
- For i = 2 To UBound(arr)
- n = Day(arr(i, 4))
- s = arr(i, 2) & arr(i, 3)
- s2 = arr(i, 2) & arr(i, 3) & n
- dz(s2) = arr(i, 5)
- If Not d.exists(s) Then
- m = m + 1
- d(s) = m
- brr(m, 1) = arr(i, 3)
- brr(m, 2) = arr(i, 2)
- brr(m, 3) = "首次打卡"
- brr(m + 1, 3) = "末次打卡"
- brr(m + 2, 3) = "工时"
- m = m + 2
- End If
- Next
- For i = UBound(arr) To 2 Step -1
- n = Day(arr(i, 4))
- s2 = arr(i, 2) & arr(i, 3) & n
- ds(s2) = arr(i, 5)
- Next
- For i = 2 To m Step 3
- For j = 4 To 34
- s = brr(i, 2) & brr(i, 1) & brr(1, j)
- brr(i, j) = ds(s)
- brr(i + 1, j) = dz(s)
- If brr(i + 1, j) >= brr(i, j) Then
- brr(i + 2, j) = brr(i + 1, j) - brr(i, j)
- Else
- brr(i + 2, j) = 0
- End If
- Next
- Next
- [a2].Resize(m, 34) = brr
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|