|
Sub lqxs()
Dim arr, i&, j&
Dim d, k, yy
Dim sht As Worksheet
Set d = CreateObject("scripting.dictionary")
Sheet2.Activate
For Each sht In Sheets
If InStr(sht.name, "明细") Then
yy = Left(sht.name, Len(sht.name) - 2)
arr = sht.[a1].CurrentRegion
For i = 2 To UBound(arr, 2) Step 3
If arr(2, i) <> "" Then
If Not d.exists(arr(2, i)) Then
Set d(arr(2, i)) = CreateObject("scripting.dictionary")
End If
For j = 4 To 34
If arr(j, i + 1) <> "" Then
d(arr(2, i))(yy) = d(arr(2, i))(yy) + arr(j, i + 1)
End If
Next
End If
Next
End If
Next
[b2].Resize(13, 500).ClearContents
[b2].Resize(1, d.Count) = k
arr = Sheet2.UsedRange
For j = 2 To UBound(arr, 2)
For i = 3 To 14
If d.exists(arr(2, j)) Then
If d(arr(2, j)).exists(arr(i, 1)) Then
arr(i, j) = d(arr(2, j))(arr(i, 1))
End If
End If
Next
Next
Sheet2.[b1].Resize(12, d.Count) = arr
Sheet3.[b2].Resize(1, 500).ClearContents
Sheet3.[b2].Resize(1, d.Count) = k
End Sub |
|