不明确的名称 - 工作表更改 - 双

Question

我试图将第二个代码添加到单个工作表中，但不断收到“检测到不明确的名称”错误。意识到我需要组合这两个代码，但这样做遇到困难。这是两个代码，一个在另一个下面（我必须制作第二个子代码，因为单元格可以手动输入）：

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    If Target.Value = "" Then Exit Sub
    
    On Error Resume Next
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
        Exit Sub
    End If
    On Error GoTo 0
   
    Dim oldvalue, newvalue, sep As String
    Dim rng1 As Range, rng2 As Range

    Set rng1 = Range("B199:B218,B223:B243,B247:B261,B266:B275,F120")
    Set rng2 = Range("C199:C218,C223:C243,C247:C261,C266:C275")

    If Not Application.Intersect(Target, rng1) Is Nothing Then
        sep = " - "
    ElseIf Not Application.Intersect(Target, rng2) Is Nothing Then
        sep = vbNewLine
    Else
        Exit Sub
    End If

    Application.EnableEvents = False
    newvalue = Target.Value
    Application.Undo
    oldvalue = Target.Value
    If oldvalue = "" Then
        Target.Value = newvalue
    Else
        If InStr(1, oldvalue, newvalue) = 0 Then
            Target.Value = oldvalue & sep & newvalue
        Else
            Target.Value = oldvalue
        End If
    End If
End sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then GoTo done

If Application.Intersect(Target, ActiveSheet.Range("F117")) Is Nothing Then GoTo done
application.enableevents=False
If Target.Value = 0 Then
'T1
Range("$A$145").Value = "X"
Range("$B$145").Value = "TestD4"
Range("$C$145").Value = "00000"
Range("$D$145").Value = "00000"
Range("$E$145").Value = "TestD4"
Range("$F$145").Value = "Test D4"
'T2
'Game 0
Range("A159").Value = "X"
Range("B159").Value = "Test D4"
Range("C159").Value = "0.00"
Range("D159").Value = "0.00"
Range("E159").Value = "0.00"
Range("F159").Value = "0.00"
Range("G159").Value = "0.00"
'Game 1
Range("A172").Value = "X"
Range("B172").Value = "Test D4"
Range("C172").Value = "0.00"
Range("D172").Value = "0.00"
Range("E172").Value = "0.00"
Range("F172").Value = "0.00"
Range("G172").Value = "0.00"
'Game 2
Range("A185").Value = "X"
Range("B185").Value = "Test D4"
Range("C185").Value = "0.00"
Range("D185").Value = "0.00"
Range("E185").Value = "0.00"
Range("F185").Value = "0.00"
Range("G185").Value = "0.00"

'T10
Range("A322").Value = "X"
Range("B322").Value = "X"
Range("C322").Value = "Test D4"
Range("D322").Value = "Test D4"
Range("E322").Value = "Test D4"

Else

'T1
Range("$A$145").Value = ""
Range("$B$145").Value = ""
Range("$C$145").Value = ""
Range("$D$145").Value = ""
Range("$E$145").Value = ""
Range("$F$145").Value = ""
'T2
'Game 0
Range("A159").Value = ""
Range("B159").Value = ""
Range("C159").Value = ""
Range("D159").Value = ""
Range("E159").Value = ""
Range("F159").Value = ""
Range("G159").Value = ""
'Game 1
Range("A172").Value = ""
Range("B172").Value = ""
Range("C172").Value = ""
Range("D172").Value = ""
Range("E172").Value = ""
Range("F172").Value = ""
Range("G172").Value = ""
'Game 2
Range("A185").Value = ""
Range("B185").Value = ""
Range("C185").Value = ""
Range("D185").Value = ""
Range("E185").Value = ""
Range("F185").Value = ""
Range("G185").Value = ""

'T10
Range("A322").Value = ""
Range("B322").Value = ""
Range("C322").Value = ""
Range("D322").Value = ""
Range("E322").Value = ""

End If

done:
    Exit Sub
Application.EnableEvents = True
End Sub

Answer 1

如果您需要对更改采取多个操作，那么您可以像这样构建代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    ProcessOne Target
    ProcessTwo Target
End Sub

Sub ProcessOne(Target As Range)
    'do something with Target
End Sub

Sub ProcessTwo (Target As Range)
    'do something else with Target
End Sub