用VBA求和汇总

深秋红叶2019

用VBA实现红色框描述的效果，共四个问题，谢谢老师

3190496160

Sub test11()
Dim d As Object
Dim rs
Dim i As Integer
Dim ar As Variant
Dim br As Variant
Dim m
Set d = CreateObject("scripting.dictionary")
rs = Sheets("学员信息建档").Cells(Rows.Count, 9).End(xlUp).Row
Sheets("学员信息建档").Range("i3:l" & rs) = Empty
ar = Sheets("学员信息建档").[a1].CurrentRegion
For i = 3 To UBound(ar)
    If ar(i, 1) <> "" Then
        d(ar(i, 1)) = i
    End If
Next i
br = Sheets("报名登记").[a1].CurrentRegion
For i = 7 To UBound(br)
    m = d(br(i, 2))
    If m <> "" Then
        ar(m, 9) = ar(m, 9) + br(i, 11)
        ar(m, 10) = ar(m, 10) + 1
    End If
Next i
ar(3, 11) = UBound(ar) - 2
ar(3, 12) = UBound(br) - 6
Sheets("学员信息建档").[a1].CurrentRegion = ar
End Sub

3190496160

蓝泰学校-学员登记库存(正式).zip (33.1 KB, 下载次数: 12)

2020-1-30 17:47 上传

点击文件名下载附件

深秋红叶2019

3190496160 发表于 2020-1-30 17:47

老师，可以不用按钮就实现的吗，并且实时更新的那种，谢谢