本帖最后由 qh8600 于 2014-5-16 18:00 编辑
642070295 发表于 2014-5-16 17:45
要求把汇总结果在原列里显示,该怎么改代码呢?
Private Sub CommandButton1_Click()
Dim ar, br, str As String, d As Object, i, j, r As String '定义变量
Set d = CreateObject("Scripting.Dictionary") '字典对象
ar = Sheet1.Range("A1").CurrentRegion '把单元格赋值给变量
ReDim br(1 To UBound(ar), 1 To 3) '重新定义数组大小
For i = 2 To UBound(ar) '循环变量从2到数组的最大行
str = ar(i, 3) '把数组爱人ar的第三列赋值给变量str
If Not d.Exists(str) Then '判断字典中没有变量str
Cnt = Cnt + 1 '累加
d(str) = Cnt '写入字典
br(Cnt, 2) = ar(i, 3) '给数组br赋值
End If
r = d(str) '字典赋值给变量
If ar(i, 1) <> "" Then br(r, 1) = Round(br(r, 1) + ar(i, 2)) '金额累加
If ar(i, 3) <> "" Then br(r, 3) = Round(br(r, 3) + ar(i, 4)) '销量累加
Next i
With Sheet1
.Range(.Cells(2, 2), .Cells(UBound(ar), 4)).ClearContents '清空要写入的单元格区域
.Cells(2, 2).Resize(Cnt, 3) = br '写入数组
End With
End Sub
现在改为B2单元格为顶点的区域