|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 ruhong18 于 2019-12-2 11:32 编辑
思路1:按附件格式,提取不重复客户名合计对应三列数据;思路2:参考以下数组方式,分成2个工作表的格式,表1汇总,表2提取结果;
参考代码如下:
Sub 汇总_数组方式()
On Error Resume Next
Dim Col As New Collection
Dim Rng As Range, Arr, Brr()
Dim i As Integer, r As Integer, k%, j&
If [a3] <> "" Then Range("a3:d" & [a65536].End(3).Row).Clear
With Sheets("流水帐总表")
For Each Rng In .Range("b3", .[b65536].End(3))
If Trim(Rng) <> "" Then Col.Add Rng, key:=CStr(Rng)
Next
Arr = .Range("b3:e" & .[b65536].End(3).Row)
j = Col.Count + 1
ReDim Brr(1 To Col.Count + 1, 1 To 4)
For i = 1 To Col.Count
k = k + 1
For r = 1 To UBound(Arr)
If Arr(r, 1) = Col(i) Then
Brr(k, 1) = k
Brr(k, 2) = Col(i)
Brr(k, 3) = Brr(k, 3) + Arr(r, 3)
Brr(j, 3) = Brr(j, 3) + Arr(r, 3)
Brr(k, 4) = Brr(k, 4) + Arr(r, 4)
Brr(j, 4) = Brr(j, 4) + Arr(r, 4)
End If
Next
Next
End With
Brr(j, 1) = "合计"
Range("c3").Resize(j, 2).NumberFormatLocal = "0.00_ "
Range("a3").Resize(j, 2).HorizontalAlignment = xlCenter
With Range("a3").Resize(j, 4)
.Value = Brr
.Borders.LineStyle = 1
.Font.Name = "Arial"
.Font.Size = 10
End With
End Sub
|
|