|
我附件上有2表,我希望得到一个vba代码,当我执行的时候,他根据分类表格中同一个人,同一天的考勤放在汇总表格同一行里,现在的汇总表格就是我希望得到的结果,谢谢高手师傅们的帮忙
- Sub Ttl()
- Dim arr
- Dim re()
- Dim d As Object, i As Long, j As Long, KeyString As String, Cnt As Long
- Set d = CreateObject("scripting.dictionary")
- arr = Sheets("分类").Range("A1:E" & Sheets("分类").Cells(Rows.Count, 1).End(xlUp).Row).Value
- ReDim re(1 To UBound(arr), 1 To 5)
- For i = 1 To UBound(arr)
- KeyString = arr(i, 1) & arr(i, 2) & arr(i, 4)
- If Not d.exists(KeyString) Then
- Cnt = Cnt + 1
- d(KeyString) = Array(Cnt, 5)
- For j = 1 To 4
- re(Cnt, j) = arr(i, j)
- Next
- Else
- d(KeyString) = Array(d(KeyString)(0), d(KeyString)(1) + 1)
- If UBound(re, 2) < d(KeyString)(1) Then ReDim Preserve re(1 To UBound(re), 1 To UBound(re, 2) + 1)
- End If
- re(d(KeyString)(0), d(KeyString)(1)) = arr(i, 5)
- Next
- With Sheets("汇总").Range("A1").Resize(Cnt, UBound(re, 2))
- .Value = re
- .Offset(, 4).NumberFormatLocal = "h:mm;@"
- End With
- End Sub
复制代码
|
|