|
5学分
本帖最后由 lifeinfo 于 2014-5-29 17:47 编辑
我做了一个仓库的进出库表格,其中入库数据表内“已发货数量支”这一列的数据需要从“出库数据”表中汇总过来,本来是想直接用VBA代码汇总,但因为我的VBA水平实在太烂,参考其他老师提供的代码修改过来后总是不行,所以暂时只好用写入公式的办法替代。麻烦老师帮忙看一下直接汇总的代码应该如何写,非常感谢!
附件请测试,为了方便对比,暂且输出在N列,确认无误后可以改到J列。 - Private Sub CommandButton1_Click()
- Dim arr, arr1, i&, d As Object, s$
- Set d = CreateObject("scripting.dictionary")
- arr = Range("d2:g" & [d65536].End(3).Row)
- arr1 = Sheets(2).Range("c2:f" & Sheets(2).[c65536].End(3).Row)
- For i = 1 To UBound(arr1)
- d(arr1(i, 1) & "," & arr1(i, 2) & "," & arr1(i, 3)) = arr1(i, 4)
- Next i
- For i = 1 To UBound(arr)
- s = arr(i, 1) & "," & arr(i, 2) & "," & arr(i, 3)
- If d.exists(s) Then arr(i, 4) = d(s) Else arr(i, 4) = 0
- Next i
- [n2].Resize(UBound(arr)) = Application.Index(arr, , 4)
- End Sub
复制代码
|
|