物料编码对应汇总

15968725770

物料编码对应汇总

山菊花

K3=SUMIF(Sheet2!A:A,A3,Sheet2!M:M)
L3=SUMIF(Sheet2!A:A,A3,Sheet2!O:O)
将公式往下拉。

15968725770

山菊花 发表于 2022-11-22 19:18
K3=SUMIF(Sheet2!A:A,A3,Sheet2!M:M)
L3=SUMIF(Sheet2!A:A,A3,Sheet2!O:O)
将公式往下拉。

能用VBA 吗，谢谢

3190496160

1. Sub 汇总()
   
2. Application.ScreenUpdating = False
   
3. Dim ar As Variant, br As Variant
   
4. Dim d As Object
   
5. Set d = CreateObject("scripting.dictionary")
   
6. With Sheets("Sheet2")
   
7.     r = .Cells(Rows.Count, 1).End(xlUp).Row
   
8.     ar = .Range("a2:o" & r)
   
9. End With
   
10. With Sheets("Sheet1")
    
11.     rs = .Cells(Rows.Count, 1).End(xlUp).Row
    
12.     If rs < 3 Then MsgBox "请现在目标表A列输入编码": End
    
13.     .Range("k3:l" & rs) = Empty
    
14.     br = .Range("a2:l" & rs)
    
15.     For i = 2 To UBound(br)
    
16.         If Trim(br(i, 1)) <> "" Then
    
17.             d(Trim(br(i, 1))) = i
    
18.         End If
    
19.     Next i
    
20.     For i = 2 To UBound(ar)
    
21.         If Trim(ar(i, 1)) <> "" Then
    
22.             xh = d(Trim(ar(i, 1)))
    
23.             If xh <> "" Then
    
24.                 br(xh, 11) = br(xh, 11) + ar(i, 13)
    
25.                 br(xh, 12) = br(xh, 12) + ar(i, 15)
    
26.             End If
    
27.         End If
    
28.     Next i
    
29.     .[k2].Resize(UBound(br), 1) = Application.Index(br, 0, 11)
    
30.     .[l2].Resize(UBound(br), 1) = Application.Index(br, 0, 12)
    
31. End With
    
32. Set d = Nothing
    
33. Application.ScreenUpdating = True
    
34. MsgBox "ok!"
    
35. End Sub

复制代码

3190496160

物料编码对应汇总.rar (20.77 KB, 下载次数: 11)

2022-11-23 10:01 上传

点击文件名下载附件