VBA编译错误：存在With块却提示"End With without With"及代码排查

问题概述 #

开发的社区呼叫数对比VBA模块出现编译错误End With without With，同时存在If语句未正确闭合、数值与单元格地址字符串直接比较等逻辑问题，以下是问题排查和修复方案：

核心错误点 #

• If语句结构混乱：多个独立If语句未用ElseIf串联，仅最后一个If有End If，导致代码层级错乱，触发With块不匹配的编译错误。
• 无效的数值比较：直接将数值nCalls与字符串"P41"（单元格地址）比较，无法获取城市平均值的实际数值。
• Range引用错误：.End("O39")用法违规，End方法需传入方向枚举值（如xlDown）而非单元格地址；Rows属性返回行对象集合，不能直接赋值给Integer类型的nCalls。
• 冗余的对话框调用：With块内重复调用frmOptions.ShowCallDialog，打断正常流程逻辑。

修正后的代码 #

Option Explicit

Sub CallsAverage()
    Dim neighbourhoodChoice(1 To 36) As Boolean
    Dim summaryChoice(1) As Boolean
    Dim neighbourhood As String, nCalls As Integer
    Dim cityAvg As Integer ' 存储城市平均值
    Dim dataRange As Range, message As String

    ' 仅调用一次对话框获取选择
    If frmOptions.ShowCallDialog(neighbourhood, nCalls) Then
        ' 根据选择确定社区名称
        If neighbourhoodChoice(1) Then
            neighbourhood = "Allentown"
        ElseIf neighbourhoodChoice(2) Then
            neighbourhood = "Black Rock"
        ElseIf neighbourhoodChoice(3) Then
            neighbourhood = "BroadwayFillmore"
        ' 补充其他32个社区的判断逻辑
        ElseIf neighbourhoodChoice(35) Then
            neighbourhood = "West Hertel"
        Else
            neighbourhood = "West Side"
        End If

        With Worksheets(neighbourhood)
            ' 计算O列O5到O39的呼叫总数（若需统计行数则用.Rows.Count）
            nCalls = Application.Sum(.Range("O5:O39"))
            ' 获取P41单元格的城市平均值
            cityAvg = .Range("P41").Value
            
            ' 生成提示信息并弹出对话框
            message = "The number of calls in " & neighbourhood & " in 2021 were " & nCalls & vbCrLf
            If nCalls > cityAvg Then
                MsgBox message & "The neighbourhood has more calls than the overall average, suggesting it is more problematic or less safe than most city neighbourhoods."
            ElseIf nCalls < cityAvg Then
                MsgBox message & "The neighbourhood has fewer calls than the overall average, suggesting it is less problematic or safer than most city neighbourhoods."
            Else
                MsgBox message & "The neighbourhood's call count equals the overall average, indicating it is neither more nor less problematic/safe than most city neighbourhoods."
            End If
        End With
    End If
End Sub

关键修改说明 #

1. 用ElseIf串联所有社区判断分支，确保每个条件都有正确的闭合逻辑，修复With块层级错乱问题。
2. 新增cityAvg变量读取P41单元格数值，实现数值与数值的有效比较。
3. 修正Range引用逻辑：用Application.Sum计算呼叫总数（可根据实际需求替换为行数统计），移除错误的.End("O39")写法。
4. 移除冗余的对话框调用，简化流程。
5. 完善提示信息，补充呼叫数具体数值，提升信息直观性。

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