Excel VBA中While Wend循环无法终止问题求助

问题根源 #

1. 未初始化循环变量i：原代码未给i设置起始值，VBA默认i=0，访问Cells(0,4)会触发异常的行号处理，导致循环逻辑混乱。
2. 无行号限制的循环条件：仅靠Cells(i,4) <> ""判断终止，若D列存在公式返回的空字符串（如=""），或循环到工作表最后一行后继续递增i，会导致循环无法终止。

修复方案 #

方案1：使用确定范围的For循环（推荐） #

先获取D列最后一个非空单元格的行号，再遍历处理，彻底避免无限循环：

Sub FillFormula()
    Dim lastRow As Long
    Dim i As Long
    
    ' 获取D列最后一个非空行的行号
    lastRow = Cells(Rows.Count, 4).End(xlUp).Row
    ' 设置起始行（如果有表头，改为i=2）
    For i = 1 To lastRow
        ' 仅处理D列非空的单元格
        If Cells(i, 4).Value <> "" Then
            Cells(i, 5).FormulaR1C1 = "=IFERROR(IF(RC[-3]=R[-1]C[-3],"""",IF(AND(RC[-3]=R[1]C[-3],RC[-3]=R[2]C[-3],RC[-3]=R[3]C[-3],RC[-3]=R[4]C[-3],RC[-3]=R[5]C[-3],RC[-3]=R[6]C[-3]),SUMPRODUCT(RC[-2]:R[4]C[-2],RC[-1]:R[6]C[-1])/SUM(RC[-2]:R[6]C[-2]),IF(AND(RC[-3]=R[1]C[-3],RC[-3]=R[2]C[-3],RC[-3]=R[3]C[-3],RC[-3]=R[4]C[-3],RC[-3]=R[5]C[-3]),SUMPRODUCT(RC[-2]:R[4]C[-2],RC[-1]:R[5]C[-1])/SUM(RC[-2]:R[5]C[-2]),IF(AND(RC[-3]=R[1]C[-3],RC[-3]=R[2]C[-3],RC[-3]=R[3]C[-3],RC[-3]=R[4]C[-3]),SUMPRODUCT(RC[-2]:R[4]C[-2],RC[-1]:R[4]C[-1])/SUM(RC[-2]:R[4]C[-2]),IF(AND(RC[-3]=R[1]C[-3],RC[-3]=R[2]C[-3],RC[-3]=R[3]C[-3]),SUMPRODUCT(RC[-2]:R[3]C[-2],RC[-1]:R[3]C[-1])/SUM(RC[-2]:R[3]C[-2]),IF(AND(RC[-3]=R[1]C[-3],RC[-3]=R[2]C[-3]),SUMPRODUCT(RC[-2]:R[2]C[-2],RC[-1]:R[2]C[-1])/SUM(RC[-2]:R[2]C[-2]),IF(RC[-3]=R[1]C[-3],SUMPRODUCT(RC[-2]:R[1]C[-2],RC[-1]:R[1]C[-1])/SUM(RC[-2]:R[1]C[-2]),RC[-1]))))))),"""")"
        End If
    Next i
End Sub

方案2：修正While循环逻辑 #

如果坚持使用While循环，需初始化i并添加行号限制：

Sub FillFormulaWithWhile()
    Dim i As Long
    
    ' 初始化起始行（根据实际数据位置修改）
    i = 1
    ' 添加行号限制，防止i无限增大
    While i <= Rows.Count And Cells(i, 4).Value <> ""
        Cells(i, 5).FormulaR1C1 = "=IFERROR(IF(RC[-3]=R[-1]C[-3],"""",IF(AND(RC[-3]=R[1]C[-3],RC[-3]=R[2]C[-3],RC[-3]=R[3]C[-3],RC[-3]=R[4]C[-3],RC[-3]=R[5]C[-3],RC[-3]=R[6]C[-3]),SUMPRODUCT(RC[-2]:R[4]C[-2],RC[-1]:R[6]C[-1])/SUM(RC[-2]:R[6]C[-2]),IF(AND(RC[-3]=R[1]C[-3],RC[-3]=R[2]C[-3],RC[-3]=R[3]C[-3],RC[-3]=R[4]C[-3],RC[-3]=R[5]C[-3]),SUMPRODUCT(RC[-2]:R[4]C[-2],RC[-1]:R[5]C[-1])/SUM(RC[-2]:R[5]C[-2]),IF(AND(RC[-3]=R[1]C[-3],RC[-3]=R[2]C[-3],RC[-3]=R[3]C[-3],RC[-3]=R[4]C[-3]),SUMPRODUCT(RC[-2]:R[4]C[-2],RC[-1]:R[4]C[-1])/SUM(RC[-2]:R[4]C[-2]),IF(AND(RC[-3]=R[1]C[-3],RC[-3]=R[2]C[-3],RC[-3]=R[3]C[-3]),SUMPRODUCT(RC[-2]:R[3]C[-2],RC[-1]:R[3]C[-1])/SUM(RC[-2]:R[3]C[-2]),IF(AND(RC[-3]=R[1]C[-3],RC[-3]=R[2]C[-3]),SUMPRODUCT(RC[-2]:R[2]C[-2],RC[-1]:R[2]C[-1])/SUM(RC[-2]:R[2]C[-2]),IF(RC[-3]=R[1]C[-3],SUMPRODUCT(RC[-2]:R[1]C[-2],RC[-1]:R[1]C[-1])/SUM(RC[-2]:R[1]C[-2],RC[-1]))))))),"""")"
        i = i + 1
    Wend
End Sub

关键说明 #

• 优先选择For循环：通过End(xlUp)获取最后一行，能精准限定处理范围，避免无效循环。
• 单元格非空判断：使用Cells(i,4).Value <> ""而非直接Cells(i,4) <> ""，能正确识别公式返回的空字符串，避免误判。

内容的提问来源于stack exchange，提问作者diogoferreira.ac