'看不太懂。就是根据已有或未有分别对相同人员工资等汇总的吧
'一楼附件,,,
Option Explicit
Sub test()
Dim arr, brr, crr, i, j, dic(2), m1, m2
For i = 0 To UBound(dic)
Set dic(i) = CreateObject("scripting.dictionary")
Next
arr = Sheets("系统已有人员名单").[a1].CurrentRegion
For i = 2 To UBound(arr, 1)
dic(0)(arr(i, 2)) = 1
Next
arr = Sheets("本月人员名单").[a1].CurrentRegion.Resize(, 5)
ReDim brr(1 To UBound(arr, 1), 1 To UBound(arr, 2))
crr = brr
For i = 2 To UBound(arr, 1)
If dic(0).exists(arr(i, 2)) Then
If dic(1).exists(arr(i, 2)) Then
For j = 3 To UBound(arr, 2)
brr(dic(1)(arr(i, 2)), j) = brr(dic(1)(arr(i, 2)), j) + arr(i, j)
Next
Else
m1 = m1 + 1: dic(1)(arr(i, 2)) = m1
For j = 1 To UBound(arr, 2)
brr(m1, j) = arr(i, j)
Next
End If
Else
If dic(2).exists(arr(i, 2)) Then
For j = 3 To UBound(arr, 2)
crr(dic(2)(arr(i, 2)), j) = crr(dic(2)(arr(i, 2)), j) + arr(i, j)
Next
Else
m2 = m2 + 1: dic(2)(arr(i, 2)) = m2
For j = 1 To UBound(arr, 2)
crr(m2, j) = arr(i, j)
Next
End If
End If
Next
With Sheets("系统已有人员")
.[b:b].NumberFormatLocal = "@"
.[a2].Resize(UBound(brr, 1), UBound(brr, 2)) = brr
End With
With Sheets("系统未有人员")
.[b:b].NumberFormatLocal = "@"
.[a2].Resize(UBound(crr, 1), UBound(crr, 2)) = crr
End With
End Sub |