- Sub tt()
-
- Dim Sht1 As Worksheet, Sht2 As Worksheet, d As Object
- Dim LR1 As Long, LR2 As Long, i As Long, j As Long, n As Long, m As Long
- Dim Arr1, Arr2, Arr()
-
- Set Sht1 = Sheets("入库列表")
- Set Sht2 = Sheets("出库列表")
- Set d = CreateObject("scripting.dictionary")
- LR1 = Sht1.Cells(Sht1.Rows.Count, 1).End(xlUp).Row
- LR2 = Sht2.Cells(Sht2.Rows.Count, 1).End(xlUp).Row
- Arr1 = Sht1.Range("G4:J" & LR1)
- Arr2 = Sht2.Range("G4:J" & LR2)
- ReDim Arr(1 To 6, 1 To LR1 + LR2)
-
- For i = 1 To UBound(Arr1)
- If Not d.exists(Arr1(i, 1) & Arr1(i, 2)) Then
- n = n + 1
- d(Arr1(i, 1) & Arr1(i, 2)) = n
- Arr(1, n) = Arr1(i, 1) '物料名称
- Arr(2, n) = Arr1(i, 2) '物料规格
- Arr(3, n) = Arr1(i, 3) '单位
- Arr(5, n) = Arr1(i, 4) '入库数量 Arr(4,n)为上月结存,留空
- Else
- m = d(Arr1(i, 1) & Arr1(i, 2))
- Arr(5, m) = Arr(5, m) + Arr1(i, 4)
- End If
- Next i
- For j = 1 To UBound(Arr2)
- If Not d.exists(Arr2(j, 1) & Arr2(j, 2)) Then
- n = n + 1
- d(Arr2(j, 1) & Arr2(j, 2)) = n
- Arr(1, n) = Arr2(j, 1) '物料名称
- Arr(2, n) = Arr2(j, 2) '物料规格
- Arr(3, n) = Arr2(j, 3) '单位
- Arr(6, n) = Arr2(j, 4) '出库数量
- Else
- m = d(Arr2(j, 1) & Arr2(j, 2))
- Arr(6, m) = Arr(6, m) + Arr2(j, 4)
- End If
- Next j
- Range("B4").Resize(n, 6) = Application.Transpose(Arr)
- For i = 4 To Cells(Rows.Count, 2).End(xlUp).Row
- Range("H" & i).Formula = "=E" & i & "-F" & i & "-G" & i
- Next i
- End Sub
复制代码 |