|
Sub AwTest()
Dim i&, kStr$, ws As Worksheet, arr, d As Object
Application.ScreenUpdating = False
Set d = CreateObject("Scripting.Dictionary")
For Each ws In Sheets
If InStr(ws.Name, "仓库") Then
arr = ws.Range("C3:K" & ws.Cells(Rows.Count, 3).End(3).Row)
For i = 1 To UBound(arr)
kStr = arr(i, 1) & "|" & arr(i, 2)
d(kStr) = arr(i, UBound(arr, 2))
Next
End If
Next
For Each ws In Sheets
If InStr(ws.Name, "流水") Then
ws.[i7:j5000].ClearContents
arr = ws.Range("D7:I" & ws.Cells(Rows.Count, 4).End(3).Row)
For i = 1 To UBound(arr)
kStr = arr(i, 1) & "|" & arr(i, 2)
If d.exists(kStr) Then arr(i, 5) = d(kStr): arr(i, 6) = arr(i, 5) * arr(i, 4)
Next
ws.Range("D7:I" & ws.Cells(Rows.Count, 4).End(3).Row) = arr
End If
Next
Application.ScreenUpdating = True
End Sub |
|