vba - 在 Worksheet_Change 中将 Select Case 组合为单元格和范围

Question

我有一个当前工作表，当某个范围内的值发生更改时，该工作表需要具有另一个工作表中的值。
另外，我需要观察某个单元格值来执行另一个操作，在这种情况下，显示一个 Msgbox。

我正在使用 Worksheet_Change(ByVal Target As Range)事件，但当我指定 Select Case "$G$6" 时整个代码不起作用和 Case "$G$24:$H$54"并尝试了Case Else但没有用。

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Select Case Target.Address

        Case "$G$6"
            If InStr(1, Range("G6"), "PUMP") > 0 Then
                MsgBox ("Pump")
            ElseIf InStr(1, Range("G6"), "SKID") > 0 Then
                MsgBox ("Skid")
            End If

        Case "$G$24:$H$54"
        If Not Application.Intersect(Target, Range("G24:H54")) Is Nothing Then
            If InStr(1, Range("G24"), "Calculate") > 0 And InStr(1, Range("G25"), "Outside Shelter") > 0 Then
                Cells(19, 8).Value = Sheets("1").Cells(159, 6).Value
                Cells(20, 9).Value = Sheets("1").Cells(163, 6).Value
                Cells(19, 11).Value = Sheets("1").Cells(160, 6).Value
                Cells(20, 10).Value = Sheets("1").Cells(164, 6).Value
            ElseIf InStr(1, Range("G24"), "Calculate") > 0 And InStr(1, Range("G25"), "Inside Shelter") > 0 Then
                Cells(19, 8).Value = Sheets("1").Cells(182, 6).Value
                Cells(20, 9).Value = Sheets("1").Cells(187, 6).Value
                Cells(19, 11).Value = Sheets("1").Cells(183, 6).Value
                Cells(20, 10).Value = Sheets("1").Cells(188, 6).Value
            End If
        End If

        Application.EnableEvents = True
        Application.ScreenUpdating = True

    End Select
End Sub

Answer 1

这是一个可能的解决方案:

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Select Case True

    Case Not Intersect(Target, Range("G6")) Is Nothing
        If InStr(1, Range("G6"), "PUMP") > 0 Then
            MsgBox ("Pump")
        ElseIf InStr(1, Range("G6"), "SKID") > 0 Then
            MsgBox ("Skid")
        End If

    Case Not Intersect(Target, Range("G24:H54")) Is Nothing
        If InStr(1, Range("G24"), "Calculate") > 0 _
                    And InStr(1, Range("G25"), "Outside Shelter") > 0 Then
            Cells(19, 8).Value = Sheets("1").Cells(159, 6).Value
            Cells(20, 9).Value = Sheets("1").Cells(163, 6).Value
            Cells(19, 11).Value = Sheets("1").Cells(160, 6).Value
            Cells(20, 10).Value = Sheets("1").Cells(164, 6).Value
        ElseIf InStr(1, Range("G24"), "Calculate") > 0 _
                    And InStr(1, Range("G25"), "Inside Shelter") > 0 Then
            Cells(19, 8).Value = Sheets("1").Cells(182, 6).Value
            Cells(20, 9).Value = Sheets("1").Cells(187, 6).Value
            Cells(19, 11).Value = Sheets("1").Cells(183, 6).Value
            Cells(20, 10).Value = Sheets("1").Cells(188, 6).Value
        End If

    End Select

    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

这个想法是使用 Select Case True ，选择 Not Intersect(Range1, Range2) Is Nothing .一般来说，最好使用 Range("G6")并将其与 Target 进行比较与 $G$6 相比并将其与 Target.Address 进行比较.