我需要將以下 3 個子例程組合到一個作業表更改事件中，但我不確定如何。
我嘗試在作業表編輯器中撰寫一個子程式，在作業簿編輯器中撰寫另一個子程式。但是，鑒于我有 3 個子例程都參考同一個作業表，我不確定如何組合它們。任何幫助是極大的贊賞！
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D3:D100")) Is Nothing Then
Exit Sub
Else
Dim i As Integer
For i = 3 To 100
If Range("D" & i).Value = "Remote" Then
Range("O" & i).Value = "N/A"
Range("P" & i).Value = "N/A"
Range("Q" & i).Value = "N/A"
End If
Next i
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target,Range("H3:H100")) Is Nothing Then
Exit Sub
Else
Dim e As Integer
For e = 3 To 100
If Range("H" & e).Value = 1 Then
Range("I" & e).Value = "N/A"
End If
Next e
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target,Range("I3:I100")) Is Nothing Then
Exit Sub
Else
Dim e As Integer
For e = 3 To 100
If Range("I" & e).Value = 1 Then
Range("H" & e).Value = "N/A"
End If
Next e
End If
End Sub
uj5u.com熱心網友回復：
翻轉邏輯。
If Intersect(Target, Range("D3:D100")) Is Nothing Then
Exit Sub
Else
...
End If
將此更改為
If Not Intersect(Target, Range("D3:D100")) Is Nothing Then
' Remove Exit Sub
' Remove Else
...
End If
對另外兩個Intersect呼叫執行相同的操作，然后將所有內容合并到一個Worksheet_Change處理程式中。
您很可能也想禁用事件，以避免在寫入作業表時重新觸發事件：
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo SafeExit
Application.EnableEvents = False
' Your three Intersect checks
SafeExit:
Application.EnableEvents = True
End Sub
uj5u.com熱心網友回復：
嘗試這個。把它放在作業表中，而不是作業簿中
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Integer
If Not Intersect(Target, Range("D3:D100")) Is Nothing Then
c = 1
Else
If Not Intersect(Target, Range("H3:H100")) Is Nothing Then
c = 2
Else
If Not Intersect(Target, Range("I3:I100")) Is Nothing Then
c = 3
End If
End If
End If
Select Case c
Case 1
' your stuff
Case 2
'your stuff
Case 3
'your stuff
Case Else
End Select
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/328266.html