我寫了一些處理觸發器的代碼。主要思想是為作業表的單元格創建動態觸發器，并根據單元格的值，計數器從所選總數中添加或減去。讓我用一個例子和代碼來讓它更清楚：這就是我的 excel 表的顯示方式。
A B C D E
1
2
3
為了限制范圍，我有 4 列和 3 行，我想將觸發器分配給 A1、B1、C1、D1、E1 等。因此我寫了這個動態地為單元格分配觸發器。
For j = 1 To 3 ' row
For I = 1 To 4 ' columns
If Target.Column = I And Target.Row = j Then
If Target.Value = "Yes" Then
Yes_4 = Yes_4 1
ElseIf Target.Value = "No" Then
No_4 = No_4 1
ElseIf Target.Value = "Not applicable" Then
Not_Applicable_4 = Not_Applicable_4 1
End If
If Target.Value = "Green - Sufficient" Then
Green_4 = Green_4 1
ElseIf Target.Value = "Orange - Largely sufficient with points for follow-up" Then
Orange_4 = Orange_4 1
ElseIf Target.Value = "Red - Insufficient" Then
Red_4 = Red_4 1
End If
Range("O" & j).Value = "The ratings you selected are as follows:" vbNewLine "Yes: " & Str(Yes_4) vbNewLine "No: " & Str(No_4) vbNewLine "Green: " & Str(Green_4) _
vbNewLine "Orange: " & Str(Orange_4) vbNewLine "Red: " & Str(Red_4) vbNewLine "No Applicable: " & Str(Not_Applicable_4)
End If
Next I
Next j
如果單元格的填充值為“是”，則yes變數變為yes 1。不僅對于一個單元格，對于行中的每個單元格，在O列中都給出了匯總。例如：yes= 1, no = 3、gree=2，以此類推。它運行良好。問題是，當在單元格中選擇“是”時，O 列中的值顯示是 = 1，但是如果您改變主意并將其從“是”更改為“否”，則 O 列中的值顯示即是 = 1，否 =1。而不是“否”= 1 和“是”= 0。
uj5u.com熱心網友回復：
將公式分配給參考適當范圍的結果單元格，并使用適當的值，例如...
=countif(your range spec, "Yes")
=countif(your range spec, "Green")
如果您需要根據資料條件調整范圍規范，您可以根據范圍的大小更新 VBA 中的公式。否則你不需要 vba 來計算總數。
uj5u.com熱心網友回復：
如果添加一個全域變數來記錄 SelectionChange 上單元格的當前值，則可以檢查更改并相應地減少計數器。
Dim sPrevious as string
Public Sub Worksheet_SelectionChange(ByVal Target As Range)
sPrevious = Target.Value
End Sub
然后添加到現有的 Change 事件中：
Private Sub Worksheet_Change(ByVal Target As Range)
For j = 1 To 3 ' row
For I = 1 To 4 ' columns
If Target.Column = I And Target.Row = j Then
If sPrevious <> Target.Value Then ''check if value has changed
If sPrevious = "Yes" Then
yes_4 = yes_4 - 1
ElseIf sPrevious = "No" Then
no_4 = no_4 - 1
ElseIf sPrevious = "Not applicable" Then
Not_Applicable_4 = Not_Applicable_4 - 1
End If
If sPrevious = "Green - Sufficient" Then
Green_4 = Green_4 - 1
ElseIf sPrevious = "Orange - Largely sufficient with points for follow-up" Then
Orange_4 = Orange_4 - 1
ElseIf sPrevious = "Red - Insufficient" Then
Red_4 = Red_4 - 1
End If
End If
uj5u.com熱心網友回復：
這是使用字典跟蹤計數并存盤訊息的顯示值的不同方法：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCheck As Range, rng As Range, rw As Range, c As Range, k, tmp
Dim dict As Object, msg As String, v
Set dict = GetTrackerDict()
Set rngCheck = Me.Range("A2:D10")
For Each rw In rngCheck.Rows
'change in this row?
If Not Application.Intersect(rw, Target) Is Nothing Then
For Each c In rw.Cells
v = c.Value
If dict.exists(v) Then 'counting this value?
tmp = dict(v) 'get array from dict
tmp(1) = tmp(1) 1 'increment count
dict(v) = tmp 'return array
End If
Next c
'Build the message
msg = "The ratings you selected are as follows:"
For Each k In dict
tmp = dict(k)
msg = msg & vbNewLine & tmp(0) & ": " & tmp(1)
Next k
rw.EntireRow.Columns("O").Value = msg
Set dict = GetTrackerDict() 'reset tracking
End If
Next rw
End Sub
'Return a dictionary and with keys as the values to be counted
Function GetTrackerDict() As Object
Dim dict As Object
' (add in the order to be displayed)
Set dict = CreateObject("scripting.dictionary")
dict.Add "Yes", Array("Yes", 0) 'array = (displayName, count)
dict.Add "No", Array("No", 0)
dict.Add "Green - Sufficient", Array("Green", 0)
dict.Add "Orange - Largely sufficient with points for follow-up", Array("Orange", 0)
dict.Add "Red - Insufficient", Array("Red", 0)
dict.Add "Not applicable", Array("N/A", 0)
Set GetTrackerDict = dict
End Function
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/331474.html