|
各位老师好,上次请教了不同列产品汇总的问题。我想优化下:
原始数据如下:
D列产品的数量在G列,I列产品的数量在J列,F列为安装部位
希望汇总后:
原来的程序如下:
Sub 汇总()
Set d = CreateObject("scripting.dictionary")
r = Cells(Rows.Count, "D").End(3).Row
arr = [D1].Resize(r, 2)
For j = 2 To UBound(arr)
If Len(arr(j, 1)) > 0 Then
d(arr(j, 1)) = d(arr(j, 1)) + arr(j, 2)
End If
Next j
r = Cells(Rows.Count, "I").End(3).Row
arr = [I1].Resize(r, 2)
For j = 2 To UBound(arr)
If Len(arr(j, 1)) > 0 Then
d(arr(j, 1)) = d(arr(j, 1)) + arr(j, 2)
End If
Next j
With Sheets("汇总")
.[B4:C4].Value = .[a1:b1].Value
.[B5].Resize(d.Count) = WorksheetFunction.Transpose(d.keys)
.[C5].Resize(d.Count) = WorksheetFunction.Transpose(d.items)
End With
End Sub
EXCEL文件请见附件,谢谢老师指导。
|
|