VBA中Intersect跨两列判断触发对象变量未设置错误求助

错误原因 #

你写的判断语句存在语法逻辑错误：

If Intersect(Target, ActiveSheet.Columns(7)) Or Intersect(Target, ActiveSheet.Columns(11)) Is Nothing Then

VBA中Intersect返回的是Range对象，不能直接用Or将其与布尔表达式（Is Nothing）连接。当Intersect(Target, Columns(7))返回Nothing时，VBA尝试将空对象转换为布尔值，就会触发「Object variable or With block variable not set」错误。

你的核心需求是：仅当Target修改的是第7列或第11列时，才执行后续代码；否则跳转到SwiftExit，因此需要重新构造正确的布尔判断逻辑。

解决方法 #

有两种简洁可行的修正方式：

方式1：分别判断两列交集，用And连接 #

明确判断Target既不在第7列、也不在第11列时才跳转：

If Intersect(Target, ActiveSheet.Columns(7)) Is Nothing And Intersect(Target, ActiveSheet.Columns(11)) Is Nothing Then
    GoTo SwiftExit
End If

方式2：用Union合并两列，一次判断交集 #

将第7列和第11列合并为一个Range对象，再判断Target是否与该合并区域有交集，写法更简洁高效：

If Intersect(Target, Union(ActiveSheet.Columns(7), ActiveSheet.Columns(11))) Is Nothing Then
    GoTo SwiftExit
End If

修正后的完整代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CurrSheetName As String
    CurrSheetName = ActiveSheet.Name

    ' 修正后的判断语句（二选一即可）
    ' 方式1：
    If Intersect(Target, ActiveSheet.Columns(7)) Is Nothing And Intersect(Target, ActiveSheet.Columns(11)) Is Nothing Then
        GoTo SwiftExit
    End If
    ' 方式2：
    ' If Intersect(Target, Union(ActiveSheet.Columns(7), ActiveSheet.Columns(11))) Is Nothing Then
    '     GoTo SwiftExit
    ' End If

    Dim NameCount As Variant
    Dim Row As Integer
    Dim DateList As Range
    Dim CalendarYear As Variant
    Dim Month As Variant
    Dim RowCount As Integer
    Dim NameCheck As String
    Dim EADOccurances As Object
    Dim T1Occurances As Object
    Dim nr As Integer
    Dim nb As Integer
    Dim SheetName As String
    Dim PersonName As String
    Dim T1Error As String
    Dim EADError As String ' 补充原代码未声明的变量

    ActiveSheet.Columns(25).ClearContents
    ActiveSheet.Columns(26).ClearContents

    Row = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

    CalendarYear = Array("January", "February", "March", "April", "May", "June", _
                        "July", "August", "September", "October", "November", "December")
    Set EADOccurances = CreateObject("System.Collections.ArrayList")
    Set T1Occurances = CreateObject("System.Collections.ArrayList")

    For Each Sheet In ActiveWorkbook.Worksheets
        ' 修正原逻辑错误：Or改为And，否则会错误遍历所有工作表
        If Sheet.Name <> "Info" And Sheet.Name <> "Data" Then
            SheetName = Sheet.Name
            RowCount = 0
            Set DateList = Sheet.Range("A2:A" & Row)

            For Each MonthNr In DateList
                RowCount = RowCount + 1
                MonthNr = Format(MonthNr, "mmmm")

                PersonName = Sheet.Cells(RowCount + 1, 7).Value
                EADError = Sheet.Cells(RowCount + 1, 5).Value
                T1Error = Sheet.Cells(RowCount + 1, 9).Value

                For d = 0 To 11
                    If MonthNr = CalendarYear(d) And Sheet.Cells(RowCount + 1, 7).Value = CurrSheetName And Sheet.Cells(RowCount + 1, 5).Value = "Yes" Then
                        EADOccurances.Add MonthNr
                    End If

                    If MonthNr = CalendarYear(d) And Sheet.Cells(RowCount + 1, 11).Value = CurrSheetName And Sheet.Cells(RowCount + 1, 9).Value = "Yes" Then
                        T1Occurances.Add MonthNr
                    End If
                Next
            Next
        End If
    Next

    nr = 0
    For i = 1 To EADOccurances.Count
        Worksheets(CurrSheetName).Cells(i, 25) = EADOccurances(nr)
        nr = nr + 1
    Next i

    nb = 0
    Dim x As Integer
    For x = 1 To T1Occurances.Count
        Worksheets(CurrSheetName).Cells(x, 26) = T1Occurances(nb)
        nb = nb + 1
    Next x

    With Worksheets(CurrSheetName)
        Dim MyEADCount As Integer
        Dim MyT1Count As Integer
        Dim RowCounter As Integer

        For d = 0 To 11
            MyEADCount = Application.WorksheetFunction.CountIf(.Range("Y1:Y1000"), CalendarYear(d))
            MyT1Count = Application.WorksheetFunction.CountIf(.Range("Z1:Z1000"), CalendarYear(d))

            .Range("U" & d + 2).Value = MyEADCount
            .Range("W" & d + 2).Value = MyT1Count
        Next
    End With

SwiftExit:
End Sub

注：额外修正了原代码两处小问题：

1. 补充了未声明的EADError变量；
2. 将工作表判断的Or改为And，原写法会错误遍历所有工作表，无法排除指定表。

内容的提问来源于stack exchange，提问作者Drawleeh