每當特定型別的單元格的值發生變化時，我想清除其下方的單元格。由于此值可以是任何字串，因此我正在通過其左側一列的標簽搜索它，并使用此標簽通過該標簽查找所有實體
Function FindAll(rng As Range, What As Variant, Optional LookIn As XlFindLookIn = xlValues, Optional LookAt As XlLookAt = xlWhole, Optional SearchOrder As XlSearchOrder = xlByColumns, Optional SearchDirection As XlSearchDirection = xlNext, Optional MatchCase As Boolean = False, Optional MatchByte As Boolean = False, Optional SearchFormat As Boolean = False) As Range
Dim SearchResult As Range
Dim firstMatch As String
With rng
Set SearchResult = .Find(What, , LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
If Not SearchResult Is Nothing Then
firstMatch = SearchResult.Address
Do
If FindAll Is Nothing Then
Set FindAll = SearchResult
Else
Set FindAll = Union(FindAll, SearchResult)
End If
Set SearchResult = .FindNext(SearchResult)
Loop While Not SearchResult Is Nothing And SearchResult.Address <> firstMatch
End If
End With
End Function
然后我嘗試向這樣的所有實體添加一個 worksheet_change
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SearchRange As Range, SearchResults As Range, rng As Range
Dim ws As Worksheet: Set ws = Sheets("SHEET 1")
Set SearchRange = ws.UsedRange
Set SearchResults = FindAll(SearchRange, "TESTVALUE")
If SearchResults Is Nothing Then
'No match found
Else
For Each rng.Offset(1, 0) In SearchResults
If Not Application.Intersect(rng.Offset(1, 0), Range(Target.Address)) Is Nothing Then
' Display a message when one of the designated cells has been
' changed.
' Place your code here.
rng.Offset(1, 1).ClearContents
MsgBox ("A")
End If
Next
End If
End Sub
盡管包括 MsgBox，但當我更改單元格時沒有任何反應
編輯：添加了澄清影像
當用戶更改類別下拉選單時，應清除子類別下拉選單。
uj5u.com熱心網友回復：
我認為這是一種更簡單的方法：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
'set some limit for the size of change you want to handle
If Target.Cells.CountLarge > 100 Then Exit Sub
On Error GoTo haveError 'to make sure events turned back on
Application.EnableEvents = False 'don't re-trigger the handler
For Each c In Target.Cells 'check each cell in the changed range
If c.Column > 1 Then
Select Case c.Offset(0, -1).Value
Case "Select Category:"
c.Offset(1, 0).ClearContents
Case "Select Subcategory:"
'something else here...
End Select
End If
Next c
haveError:
Application.EnableEvents = True
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/364559.html
上一篇：VBA中數字序列的正確代碼是什么
下一篇：嘗試在vba中對數字進行分組