时间戳

首页 > 解决方案 > 合并 2 个 Worksheet_Change 事件

excel - 合并 2 个 Worksheet_Change 事件

问题描述

我有两个 Worksheet_Change 事件子程序，它们可以自己完美地工作，但是我需要将它们结合起来测试两个范围“G2”或 G3“中的任何一个的条件。我已经尝试了搜索中的所有选项，但就是无法得到这个工作。我将不胜感激任何帮助或建议。以下是两个潜艇：

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("G2")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub

          Dim lr As Long
          lr = Range("B" & Rows.Count).End(xlUp).Row

    Application.ScreenUpdating = False

          Range("Z7:Z" & lr) = "=ISERROR(MATCH(G$2,B7:O7,0))"
          Range("Z7", Range("Z" & Rows.Count).End(xlUp)).AutoFilter 1, False

    Application.ScreenUpdating = True
    Call activate_button_31


End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("G3")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub

          Dim lr As Long
          lr = Range("B" & Rows.Count).End(xlUp).Row

    Application.ScreenUpdating = False

          Range("Q7:Q" & lr) = "=ISERROR(MATCH(G$3,B7:O7,0))"
          Range("Q7", Range("Q" & Rows.Count).End(xlUp)).AutoFilter 1, False

    Application.ScreenUpdating = True
    Call activate_button_40


End Sub

如果您需要任何进一步的信息，请告诉我。

亲切的问候

科尼

标签： excelvba

解决方案

尝试这个：

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    Dim lr As Long
    lr = Range("B" & Rows.Count).End(xlUp).row

    Application.ScreenUpdating = False

    If Not Intersect(Target, Range("G2")) Is Nothing Then
        Range("Z7:Z" & lr) = "=ISERROR(MATCH(G$2,B7:O7,0))"
        Range("Z7", Range("Z" & Rows.Count).End(xlUp)).AutoFilter 1, False
        Call activate_button_31

    ElseIf Not Intersect(Target, Range("G3")) Is Nothing Then
        Range("Q7:Q" & lr) = "=ISERROR(MATCH(G$3,B7:O7,0))"
        Range("Q7", Range("Q" & Rows.Count).End(xlUp)).AutoFilter 1, False
        Call activate_button_40
    End If

    Application.ScreenUpdating = True
End Sub

推荐阅读