前言
面对每月的消费账单,面对月底待还的信用卡或花呗,面对不足三位数的余额,你是否怀疑过账单自己的消费。你是否因此开始记账,每个月记流水,想知道当年中某项消费总额;
你是一名会计,只想用粗略的查看某个月、某个科目下或某个客户经济往来的总金额入;
你是一名行政人员,想了解一下当月某个员工迟到、旷工、加班情况;
你是仓库保管人员,想查看某种类的货物当年进、出量……
不需要筛选、汇总、计算,只要点击所查单元格,VBA快速帮你汇总、计算。
一、运行效果
Excel工作表单元格单击选中事件,VBA动态计算数值总和
二、代码
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim str As String
Dim targetUsedRow As Integer
Dim targetUsedCol As Integer
Dim sumTarget As String
Dim sumAll As Integer
ActiveSheet.Range("A1:IV65536").Interior.ColorIndex = xlNone
str = ActiveSheet.Cells(Target.Row, Target.Column).Value
On Error Resume Next
For i = 1 To 65536 '计算行数
If ActiveSheet.Cells(i, Target.Column).Value = "" Then
targetUsedRow = i - 1
Exit For
End If
Next
For i = 1 To 65536 '计算行数
If ActiveSheet.Cells(1, i).Value = "" Then
targetUsedCol = i - 1
Exit For
End If
Next
If str = "" Or Target.Column > 2 Then '若选中单元格为空白或超范围,退出触发事件
Exit Sub
End If
For i = 1 To targetUsedRow
If str <> "" And i <> Target.Row And ActiveSheet.Cells(i, Target.Column).Value = str Then
ActiveSheet.Cells(i, Target.Column).Interior.ColorIndex = 44
ActiveSheet.Cells(Target.Row, Target.Column).Interior.ColorIndex = 44
sumTarget = Cells(i, 3).Value
sumAll = sumAll + CInt(sumTarget)
End If
Next
sumAll = sumAll + CInt(ActiveSheet.Cells(Target.Row, 3).Value)
If str <> "" Then
ActiveSheet.Cells(1, targetUsedCol + 2) = str
ActiveSheet.Cells(1, targetUsedCol + 3) = sumAll
End If
End Sub