Zipall 发表于 2014-5-3 20:09
同一编码的商品,入库单价是否一定相同? 如果不同,汇总表的单价填什么?(first? last? average?) 出库单价同理 ...
不好意思,一不小心把原始代码都删除掉了- Sub 宏1()
- Dim arr, brr(), d As Object, i&, j&, t
- Set d = CreateObject("scripting.dictionary")
- arr = Sheets("基础资料").[a1].CurrentRegion
- ReDim brr(2 To UBound(arr), 2 To 18)
- For i = 2 To UBound(arr)
- d(arr(i, 2)) = i
- For j = 2 To 7
- brr(i, j) = arr(i, j)
- Next
- brr(i, 9) = arr(i, 8)
- Next
- arr = Sheets("出入库明细").[a1].CurrentRegion
- For i = 2 To UBound(arr)
- t = d(arr(i, 4))
- If t <> "" Then
- If arr(i, 2) <> "出库" Then
- brr(t, 10) = brr(t, 10) + arr(i, 9)
- brr(t, 12) = brr(t, 12) + arr(i, 11)
- Else
- brr(t, 13) = brr(t, 13) + arr(i, 9)
- brr(t, 15) = brr(t, 15) + arr(i, 11)
- End If
- End If
- Next
- For i = 2 To UBound(brr)
- If Len(brr(i, 10)) Then brr(i, 11) = brr(i, 12) / brr(i, 10)
- If brr(i, 10) + brr(i, 7) > 0 Then brr(i, 14) = (brr(i, 12) + brr(i, 9)) / (brr(i, 10) + brr(i, 7))
- If brr(i, 7) + brr(i, 10) - brr(i, 13) > 0 Then brr(i, 16) = brr(i, 7) + brr(i, 10) - brr(i, 13)
- If brr(i, 9) + brr(i, 12) - brr(i, 15) > 0 Then brr(i, 18) = brr(i, 9) + brr(i, 12) - brr(i, 15)
- If Len(brr(i, 16)) Then brr(i, 17) = brr(i, 18) / brr(i, 16)
- Next
- ActiveSheet.UsedRange.Offset(7).ClearContents
- [b8].Resize(d.Count, 17) = brr
- End Sub
复制代码 |