|
请高人帮忙修改代码
1、将《会员缴费表》查询修改为《会员缴费表2》,查询结果保存到Snrrt3
2、将汇总结果行以红色显示
Private Sub CommandButton1_Click()
With Sheets("会员缴费表")
r = .[a65536].End(3).Row
org = .Range("a1:i" & r) '原数组
Application.EnableEvents = False
.[a2].Resize(r - 1, 9).Sort key1:=.[h2], key2:=.[a2], key3:=.[b2] '按H列排序
arr = .Range("a1:i" & r + 1) '读入数组(加一行,避免最后一行比较时出错)
.Range("a1:i" & r) = org '恢复原序
Application.EnableEvents = True
ReDim brr(1 To UBound(arr) + 100, 1 To 9)
Set d = CreateObject("scripting.dictionary")
For i = 2 To r
x = Join(Application.Index(arr, i), ",") '每行各列相连为key
If Not d.exists(x) Then
n = n + 1: d(x) = n
For j = 1 To 9
brr(n, j) = arr(i, j)
Next
Else
p = d(x)
brr(p, 5) = brr(p, 5) + arr(i, 5)
End If
s1 = s1 + arr(i, 5)
If arr(i, 8) <> arr(i + 1, 8) Then
n = n + 1
s = s + s1
brr(n, 1) = arr(i, 8) & "总计"
brr(n, 5) = s1
s1 = 0
End If
Next
n = n + 1
brr(n, 1) = "总计": brr(n, 5) = s
End With
With Sheet1
.Cells.Clear
.[a1].Resize(1, 9) = Array("推荐人", "会员形式", "姓名", "性别", "金额", "联系方式", "备注", "编号", "交款日期")
.[a2].Resize(n, 9) = brr
For i = 1 To n
If brr(i, 1) Like "*总计" Then .Cells(i + 1, 1).Resize(1, 4).Merge
Next
.Activate
End With
End Sub
- Sub 推荐人汇总()
- With Sheets("会员缴费表")
- r = .[a65536].End(3).Row
- org = .Range("a1:i" & r) '原数组
- Application.EnableEvents = False
- .[a2].Resize(r - 1, 9).Sort key1:=.[h2], key2:=.[a2], key3:=.[b2] '按H列排序
- arr = .Range("a1:i" & r + 1) '读入数组(加一行,避免最后一行比较时出错)
- .Range("a1:i" & r) = org '恢复原序
- Application.EnableEvents = True
- ReDim brr(1 To UBound(arr) + 100, 1 To 9)
- Set d = CreateObject("scripting.dictionary")
- For i = 2 To r
- x = Join(Application.Index(arr, i), ",") '每行各列相连为key
- If Not d.exists(x) Then
- n = n + 1: d(x) = n
- For j = 1 To 9
- brr(n, j) = arr(i, j)
- Next
- Else
- p = d(x)
- brr(p, 5) = brr(p, 5) + arr(i, 5)
- End If
- s1 = s1 + arr(i, 5)
- If arr(i, 8) <> arr(i + 1, 8) Then
- n = n + 1
- s = s + s1
- brr(n, 1) = arr(i, 8) & "总计"
- brr(n, 5) = s1
- s1 = 0
- End If
- Next
- n = n + 1
- brr(n, 1) = "总计": brr(n, 5) = s
- End With
-
- With Sheets("会员缴费表2")
- .[c3,d3,h3] = ""
- .[a5:i1000].Clear
- .[a5].Resize(n, 9) = brr
- For i = 1 To n
- If brr(i, 1) Like "*总计" Then
- tjrs = tjrs + 1 '推荐人数
- .Cells(i + 4, 1).Resize(1, 4).Merge
- .Cells(i + 4, 1).Resize(1, 5).Font.Bold = True
- End If
- Next
- .Cells(n + 4, 1).Resize(1, 5).Font.Color = vbRed
- .[a5].Resize(n, 9).Borders.LineStyle = 1
- .[c3] = d.Count: .[d3] = s: .[H3] = tjrs - 1
- .Activate
- End With
- End Sub
复制代码
|
|