|
- Sub tt()
- Dim arr, i&, j&, sht2 As Worksheet
- arr = Worksheets("出货数据").[a1].CurrentRegion '将出货数据读入数组,其中3列为客户名称,5列为产品名称,9列为产品数量,11列为金额,18列为日期
- d1 = Cells(4, 7): d2 = Cells(4, 10)
- Set d = CreateObject("scripting.dictionary") '出货
- Set dd = CreateObject("scripting.dictionary") '退货
-
- For i = 2 To UBound(arr) '读入数据,客户名+品项为key,数量为item
- If arr(i, 18) >= d1 And arr(i, 18) <= d2 And arr(i, 11) <> 0 Then '日期符合条件,总金额不为0
- xkey = arr(i, 3) & arr(i, 5): xcount = arr(i, 9)
- If xcount > 0 Then '数量大于0,出货
- d(xkey) = d(xkey) + xcount
- ElseIf xcount < 0 Then '数量小于0,退货
- dd(xkey) = dd(xkey) + Abs(xcount)
- End If
- End If
- Next
-
- [c8:cp100].ClearContents '出货表
- arr = [b7:cp100]
- For i = 2 To UBound(arr)
- If arr(i, 1) <> "" Then
- For j = 2 To UBound(arr, 2)
- If arr(1, j) <> "" Then
- xkey = arr(i, 1) & arr(1, j)
- arr(i, j) = d(xkey)
- End If
- Next
- End If
- Next
- [b7:cp100] = arr
-
- [c108:cp200].ClearContents '退货表
- arr = [b107:cp200]
- For i = 2 To UBound(arr)
- If arr(i, 1) <> "" Then
- For j = 2 To UBound(arr, 2)
- If arr(1, j) <> "" Then
- xkey = arr(i, 1) & arr(1, j)
- arr(i, j) = dd(xkey)
- End If
- Next
- End If
- Next
- [b107:cp200] = arr
-
- End Sub
复制代码 |
|