VBA：解决嵌套于For Each循环的If语句引发的Next缺失错误

问题描述 #

尝试实现预算超支部分（负值）向结余预算项（正值）的自动分摊，逻辑为遍历每行将负值填补到正值中。运行代码时触发**“Next without For”**错误，排查怀疑是未闭合的If语句，但无法定位；同时不确定代码是否存在其他逻辑问题。最终要求I列基于N:P列的分摊数据更新，输出效果为：超支项清零并标注分摊去向，结余项累加分摊数值。

原始错误代码 #

Sub SpreadMacro()

'Defining Variables

Dim Lr As Long
Dim rng As Range
Dim row1 As Range
Dim Sp1 As Long
Dim Sp2 As Long
Dim Sp3 As Long
Dim Mtch As Boolean
Dim MtchGL As String
Dim Mx As Long
Dim rng2 As Range


'Find table length
Lr = Cells(Rows.Count, "H").End(xlUp).Row
Set rng = Range("H4:H" & Lr)

'Find negative values and set spread values
For Each row1 In rng.Rows
    If row1.Value >= 0 Then
    
        GoTo Skip

    Else
    
        Set rng2 = Range("M:" & Lr)
        If Application.Evaluate("MAX(rng2)") + row1.Value < 0 Then
            
            row1.Interior.ColorIndex = 8
            GoTo Skip
        
        Else
        
            Sp1 = row1.Offset(0, 6)
            Sp2 = row1.Offset(0, 7)
            Sp3 = row1.Offset(0, 8)
            
'Find budget value than can fit the variance, and return the cell address and account
            Mtch = Application.Evaluate("CELL(""Address"",(INDEX(M:M,MATCH(TRUE,INDEX((M:M+" & row1.Offset(0, 5).Value & ">0),),0))))")
            MtchGL = Range(Mtch).Offset(0, -9)
            
'Insert spread values into the matched account
            Range(Mtch).Offset(0, 1).Formula = Range(Mtch).Offset(0, 1).Formula & "+" & Sp1
            Range(Mtch).Offset(0, 2).Formula = Range(Mtch).Offset(0, 2).Formula & "+" & Sp2
            Range(Mtch).Offset(0, 3).Formula = Range(Mtch).Offset(0, 3).Formula & "+" & Sp3
            
'Clear the negative variance from over budget account and indicate where the variance was spread
            row1.Offset(0, 5).Value = "Spread in " & MtchGL
            row1.Offset(0, 6).Value = "0"
            row1.Offset(0, 7).Value = "0"
            row1.Offset(0, 8).Value = "0"
    
    End If
    
Skip:

Next row1

End Sub

错误原因及修复步骤 #

1. 核心错误：If语句未完全闭合 #

原始代码存在3层If嵌套，但仅写了2个End If，缺失最外层Else对应的End If，导致VBA无法识别For循环的边界，触发“Next without For”错误。需在Skip:标签前添加一个End If，闭合最外层的If结构。

2. 其他逻辑/语法问题修复 #

• 变量类型错误：Mtch用于存储单元格地址字符串，不应定义为Boolean，需改为String
• 范围定义错误：Range("M:" & Lr)会引用整列M，应改为Range("M4:M" & Lr)，缩小计算范围提升效率
• Evaluate公式优化：将整列M:M改为限定范围M4:M" & Lr，避免不必要的计算
• 公式累加隐患：直接拼接"+" & Sp1会导致重复执行时公式无限累加，建议改为值累加（或用Value而非Formula）

修复后的完整代码 #

Sub SpreadMacro()
    'Defining Variables
    Dim Lr As Long
    Dim rng As Range
    Dim row1 As Range
    Dim Sp1 As Long
    Dim Sp2 As Long
    Dim Sp3 As Long
    Dim Mtch As String '修正变量类型为字符串
    Dim MtchGL As String
    Dim rng2 As Range

    'Find table length
    Lr = Cells(Rows.Count, "H").End(xlUp).Row
    Set rng = Range("H4:H" & Lr)

    'Find negative values and set spread values
    For Each row1 In rng.Rows
        If row1.Value >= 0 Then
            GoTo Skip
        Else
            '修正rng2范围为有效数据区域
            Set rng2 = Range("M4:M" & Lr)
            '优化MAX计算范围，避免整列引用
            If Application.WorksheetFunction.Max(rng2) + row1.Value < 0 Then
                row1.Interior.ColorIndex = 8
                GoTo Skip
            Else
                Sp1 = row1.Offset(0, 6).Value
                Sp2 = row1.Offset(0, 7).Value
                Sp3 = row1.Offset(0, 8).Value
                
                '修正Evaluate中的范围引用，缩小到有效区域
                Mtch = Application.Evaluate("CELL(""Address"",INDEX(M4:M" & Lr & ",MATCH(TRUE,INDEX((M4:M" & Lr & "+" & row1.Offset(0, 5).Value & ">0),),0)))")
                MtchGL = Range(Mtch).Offset(0, -9).Value
                
                '改用值累加，避免公式重复拼接
                Range(Mtch).Offset(0, 1).Value = Range(Mtch).Offset(0, 1).Value + Sp1
                Range(Mtch).Offset(0, 2).Value = Range(Mtch).Offset(0, 2).Value + Sp2
                Range(Mtch).Offset(0, 3).Value = Range(Mtch).Offset(0, 3).Value + Sp3
                
                'Clear the negative variance from over budget account and indicate where the variance was spread
                row1.Offset(0, 5).Value = "Spread in " & MtchGL
                row1.Offset(0, 6).Value = 0
                row1.Offset(0, 7).Value = 0
                row1.Offset(0, 8).Value = 0
            End If
        End If '补全缺失的End If，闭合最外层Else结构
Skip:
    Next row1
End Sub

额外注意事项 #

• 运行代码前建议先备份数据，避免误操作导致数据丢失
• 若存在多个结余项，当前代码仅会找到第一个符合条件的项进行分摊，如需分摊到多个项，需补充循环逻辑
• 确保H列（判断正负的列）、M列（结余列）的数据格式为数值型，避免Evaluate公式出错

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