|
紅色新增部份
Sub demo()
Dim month
For month = 1 To 12
If Not WorksheetExists(month & "月汇总") Then Exit For
Sheets(month & "月汇总").Select
clear
Set d = CreateObject("scripting.dictionary")
orr = Sheets(month & "月其他收入").UsedRange
For I = 6 To UBound(orr)
Key = orr(I, 5)
If d.exists(Key) Then
prev = d(Key)
For k = 9 To 18
orr(I, k) = orr(I, k) + orr(prev, k)
Next
End If
d(Key) = I
Next
arr = Sheets(month & "月工资").[a1].CurrentRegion
ReDim brr(1 To UBound(arr), 1 To 3)
ReDim crr(1 To UBound(arr), 1 To 5)
ReDim drr(1 To UBound(arr), 1 To 10)
r = 0
For I = 5 To UBound(arr)
If arr(I, 1) = "" Then Exit For
If arr(I, 4) = "遗属" Then GoTo CONTINUE
r = r + 1
Key = arr(I, 2)
brr(r, 1) = Key
brr(r, 2) = arr(I, 3)
brr(r, 3) = arr(I, 5)
crr(r, 1) = arr(I, 8)
crr(r, 2) = arr(I, 9)
crr(r, 3) = arr(I, 6)
crr(r, 4) = arr(I, 7)
crr(r, 5) = arr(I, 10)
drr(r, 1) = "": drr(r, 2) = "": drr(r, 3) = "": drr(r, 4) = "": drr(r, 5) = "": drr(r, 6) = "": drr(r, 7) = "": drr(r, 8) = "": drr(r, 9) = "": drr(r, 10) = ""
If d.exists(Key) Then
drr(r, 1) = orr(d(Key), 9)
drr(r, 2) = orr(d(Key), 10)
drr(r, 3) = orr(d(Key), 11)
drr(r, 4) = orr(d(Key), 12)
drr(r, 5) = orr(d(Key), 13)
drr(r, 6) = orr(d(Key), 14)
drr(r, 7) = orr(d(Key), 15)
drr(r, 8) = orr(d(Key), 16)
drr(r, 9) = orr(d(Key), 17)
drr(r, 10) = orr(d(Key), 18)
d.Remove Key
End If
CONTINUE:
Next
[e7].Resize(r, 3) = brr
[w7].Resize(r, 5) = crr
[h7].Resize(r, 10) = drr
ReDim Err(1 To d.Count(), 1 To 3)
ReDim frr(1 To d.Count(), 1 To 10)
rr = 0
For Each Key In d.keys()
rr = rr + 1
Err(rr, 1) = orr(d(Key), 5)
Err(rr, 2) = orr(d(Key), 4)
frr(rr, 1) = orr(d(Key), 9)
frr(rr, 2) = orr(d(Key), 10)
frr(rr, 3) = orr(d(Key), 11)
frr(rr, 4) = orr(d(Key), 12)
frr(rr, 5) = orr(d(Key), 13)
frr(rr, 6) = orr(d(Key), 14)
frr(rr, 7) = orr(d(Key), 15)
frr(rr, 8) = orr(d(Key), 16)
Next
Range("e" & 7 + r).Resize(rr, 3) = Err
Range("h" & 7 + r).Resize(rr, 10) = frr
Next
End Sub
Sub clear()
lastRow = Range("e65536").End(xlUp).Row
If lastRow < 7 Then lastRow = 7
Range("e7:q" & lastRow & ",w7:aa" & lastRow).ClearContents
End Sub
Function WorksheetExists(sName As String) As Boolean
WorksheetExists = Evaluate("ISREF('" & sName & "'!A1)")
End Function
祝順心,南無阿彌陀佛!
|
|