|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub yy()
Dim sh As Worksheet, d As Object, dic As Object, ar, br, i&, r&, s$
Set sh = ThisWorkbook.Sheets("sgm表数据")
With sh
If .AutoFilterMode Then .AutoFilterMode = False
r = .Cells(.Rows.Count, "a").End(3).Row
If r < 3 Then End
ar = .Range("a2:c" & r)
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(ar)
s = ar(i, 1)
If s <> "" Then
If d.exists(s) = False Then
d(s) = Array(ar(i, 2), ar(i, 3))
Else
br = d(s)
br(0) = br(0) + Val(ar(i, 2))
br(1) = br(1) + Val(ar(i, 2))
d(s) = br
End If
End If
Next
End With
If d.Count = 0 Then Exit Sub
Set dic = CreateObject("scripting.dictionary")
Set sh = ThisWorkbook.Sheets("报工数据")
With sh
If .AutoFilterMode Then .AutoFilterMode = False
.Range("b3:c100000") = ""
r = .Cells(.Rows.Count, "a").End(3).Row
If r < 3 Then End
Erase ar
ar = .Range("a2:c" & r)
For i = 2 To UBound(ar)
s = ar(i, 1)
If dic.exists(s) = False Then
dic(s) = ""
If d.exists(s) Then
ar(i, 2) = d(s)(0)
ar(i, 3) = d(s)(1)
Else
ar(i, 2) = ""
ar(i, 3) = ""
End If
End If
Next
.Range("a2:c" & r) = ar
End With
Set d = Nothing
Set dic = Nothing
End Sub |
|