|
楼主 |
发表于 2020-2-23 17:08
|
显示全部楼层
老师,这个统计的代码可以改为工作表事件吗,通过命令按钮计算的话查询会员余额就不方便了,因为要点击计算按钮才可以查到余额
Sub 汇总统计()
Set d = CreateObject("scripting.dictionary")
arr = Sheet1.Range("a3:a" & Sheet1.[a65536].End(3).Row)
ReDim brr(1 To UBound(arr), 1 To 3)
For i = 1 To UBound(arr)
d(arr(i, 1)) = i
Next
crr = Sheet2.Range("a6:j" & Sheet2.[a65536].End(3).Row)
For i = 1 To UBound(crr)
n = d(crr(i, 2))
If n > 0 Then
If crr(i, 9) > 0 Then brr(n, 1) = brr(n, 1) + crr(i, 9)
If crr(i, 10) > 0 Then
brr(n, 2) = brr(n, 2) + crr(i, 10)
brr(n, 3) = brr(n, 1) - brr(n, 2)
End If
End If
Next
Sheet1.[e3].Resize(UBound(brr), 3) = brr
End Sub |
|