|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 xxxxbbhiox 于 2019-9-5 20:19 编辑
Sub TEST()
Application.ScreenUpdating = False
With Sheet1
On Error Resume Next
.ShowAllData
On Error GoTo 0
r = .Cells(Rows.Count, "b").End(xlUp).Row
If r = 1 Then MsgBox "没有数据不能使用", 64, "提示": Exit Sub
arr = .Range("b2:f" & r)
End With
Set d = CreateObject("scripting.dictionary")
Set dic = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr)
If Len(arr(i, 1)) Then
dic(arr(i, 1) & "+" & arr(i, 4)) = dic(arr(i, 1) & "+" & arr(i, 4)) + 1
If d.exists(arr(i, 1) & "+" & arr(i, 4)) = False Then
d(arr(i, 1) & "+" & arr(i, 4)) = arr(i, 2) & "#" & arr(i, 5)
Else
d(arr(i, 1) & "+" & arr(i, 4)) = d(arr(i, 1) & "+" & arr(i, 4)) & "@" & arr(i, 2) & "#" & arr(i, 5)
End If
End If
Next
icount = Application.Max(dic.items)
ReDim brr(1 To d.Count, 1 To icount * 2 + 4)
tem = d.keys
tem2 = d.items
For i = 0 To UBound(tem)
brr(i + 1, 1) = Split(tem(i), "+")(0)
brr(i + 1, 2) = Split(tem(i), "+")(1)
tem3 = Split(tem2(i), "@")
For x = 0 To UBound(tem3)
brr(i + 1, x * 2 + 5) = Split(tem3(x), "#")(0)
brr(i + 1, x * 2 + 6) = Split(tem3(x), "#")(1)
Total = Total + Val(brr(i + 1, x * 2 + 6))
Next
brr(i + 1, 4) = Total
Total = 0
Next
With Sheet2
r = .Cells(Rows.Count, "A").End(xlUp).Row
If r > 1 Then .Range(.Cells(2, 2), .Cells(r, 242)).ClearContents
.Range("a2").Resize(UBound(brr), UBound(brr, 2)) = brr
MsgBox "运行完成", 64, "提示"
Set dic = Nothing: Set d = Nothing
End With
Range(Selection, Selection.End(xlDown)).Select
Rows("2:328").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin, DataOption1:=xlSortNormal
Rows("2:2006").Select
Range("c2").Select
求助详细, 在目标工作表的C列实现计数功能(每一行的数量个数作为计数结果)
|
|