Excel VBA循环求和及空单元格终止循环问题求助

AI 小北

2026-5-7

Excel VBA循环优化：终止空单元格读取 + 循环结果求和

我来帮你搞定这两个核心问题，先拆解问题再给出修改后的完整代码：

一、解决循环读取空单元格的问题

你的现有循环逻辑有两个明显的bug：

For i = 1 To 1000000 内部又执行了 i = i + 1，导致i每次循环跳两行，既会漏掉数据也会打乱终止判断
空单元格判断的时机不对，应该在读取当前行数据之前就检查是否为空，避免无效循环

推荐用Do While循环逐行检查，直到目标列（比如你关注的B列）为空时停止，这样更灵活也更高效：

Dim i As Long
i = 1 ' 从第一行数据开始
Do While Sheets("Input Values").Cells(i, "B").Value <> "" ' 检查B列是否为空
    ' 循环执行的代码...
    i = i + 1 ' 手动递增行号
Loop

二、实现每次循环结果的求和

要对每次循环的计算结果（比如你代码里最终的Output!C33和Output!D33）求和，只需要三步：

在循环前定义求和变量，初始值设为0
每次循环完成计算后，把当前结果累加到变量中
循环结束后，把总和写入指定单元格

示例代码片段：

' 定义求和变量，存储累加值
Dim sumC33 As Double, sumD33 As Double
sumC33 = 0
sumD33 = 0

' 循环内每次计算完成后累加
sumC33 = sumC33 + Sheets("Output").Range("C33").Value
sumD33 = sumD33 + Sheets("Output").Range("D33").Value

' 循环结束后写入总和（示例写入Output的E33和F33，可按需修改）
Sheets("Output").Range("E33").Value = sumC33
Sheets("Output").Range("F33").Value = sumD33

三、修改后的完整优化代码

我还优化了你的代码：去掉了低效的Copy/Paste，直接用单元格赋值大幅提升运行速度；修正了循环逻辑；加入了求和功能：

Sub TEST()
    Dim i As Long
    Dim sumC33 As Double, sumD33 As Double
    Dim wsInputValues As Worksheet, wsInputsTaken As Worksheet, wsOutput As Worksheet, wsModel As Worksheet
    
    ' 提前定义工作表对象，避免重复调用Sheets()，提升效率且更稳定
    Set wsInputValues = ThisWorkbook.Sheets("Input Values")
    Set wsInputsTaken = ThisWorkbook.Sheets("Inputs Taken")
    Set wsOutput = ThisWorkbook.Sheets("Output")
    Set wsModel = ThisWorkbook.Sheets("Model")
    
    sumC33 = 0
    sumD33 = 0
    i = 1 ' 从第一行数据开始
    
    ' 循环直到Input Values的B列为空，停止处理
    Do While wsInputValues.Cells(i, "B").Value <> ""
        ' 直接赋值替代Copy/Paste，更快更可靠
        wsInputsTaken.Range("D5").Value = wsInputValues.Range("A" & i).Value
        wsInputsTaken.Range("D6").Value = wsInputValues.Range("B" & i).Value
        wsInputsTaken.Range("D7").Value = wsInputValues.Range("C" & i).Value
        wsInputsTaken.Range("D8").Value = wsInputValues.Range("D" & i).Value
        wsInputsTaken.Range("C11").Value = wsInputValues.Range("E" & i).Value
        wsInputsTaken.Range("D11").Value = wsInputValues.Range("F" & i).Value
        wsInputsTaken.Range("C16").Value = wsInputValues.Range("G" & i).Value
        wsInputsTaken.Range("D16").Value = wsInputValues.Range("H" & i).Value
        wsInputsTaken.Range("G9").Value = wsInputValues.Range("I" & i).Value
        wsInputsTaken.Range("G10").Value = wsInputValues.Range("J" & i).Value
        wsInputsTaken.Range("G11").Value = wsInputValues.Range("K" & i).Value
        wsInputsTaken.Range("G12").Value = wsInputValues.Range("L" & i).Value
        wsInputsTaken.Range("G13").Value = wsInputValues.Range("M" & i).Value
        wsInputsTaken.Range("G14").Value = wsInputValues.Range("N" & i).Value
        
        ' 设置PUP为100%并刷新计算
        wsInputsTaken.Range("G5").Value = 1
        Application.CalculateFull
        
        ' 计算无RP的结果
        With wsOutput
            .Range("C7").Formula = "=SUMPRODUCT(" & wsModel.Name & "!BJ6:BJ365," & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365)"
            .Range("C8").Formula = "=SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BK6:BK365)"
            .Range("C10").Formula = "=SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BM6:BM365)"
            .Range("C11").Formula = "=SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BN6:BN365)"
            .Range("C12").Formula = "=SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BO6:BO365)"
            .Range("C13").Formula = "=SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BP6:BP365)"
            .Range("C14").Formula = "=SUM(" & .Name & "!C11:C13)"
            .Range("C17").Formula = "=-SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BS6:BS365)"
            .Range("C18").Formula = "=-SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BT6:BT365)"
            .Range("C19").Formula = "=-SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BU6:BU365)"
            .Range("C20").Formula = "=-SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BV6:BV365)"
            .Range("C21").Formula = "=-SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BW6:BW365)"
            .Range("C22").Formula = "=-SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BX6:BX365)"
            .Range("C23").Formula = "=-SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BY6:BY365)"
            .Range("C24").Formula = "=-SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BZ6:BZ365)"
            .Range("C25").Formula = "=-SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!CA6:CA365)"
            .Range("C26").Formula = "=-SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!CB6:CB365)"
            .Range("C5").Formula = "=" & wsModel.Name & "!BL6-" & wsModel.Name & "!BS6-" & wsModel.Name & "!BT6"
            .Range("C15").Formula = "=SUM(" & .Name & "!C7:C10," & .Name & "!C14)"
            .Range("C27").Formula = "=SUM(" & .Name & "!C17:C26)"
            .Range("C29").Formula = "=-SUM(" & wsModel.Name & "!AN6:AN365)"
            .Range("C30").Formula = "=-SUM(" & wsModel.Name & "!AP6:AP365)"
            .Range("C31").Formula = "=-" & .Name & "!C2"
            .Range("C33").Formula = "=SUM(" & .Name & "!C29:C31," & .Name & "!C27," & .Name & "!C15)"
        End With
        
        ' 转换为值，移除公式
        wsOutput.Range("C5:C33").Copy
        wsOutput.Range("C5:C33").PasteSpecial xlPasteValues
        
        ' 修改PUP为0并刷新计算
        wsInputsTaken.Range("G5").Value = 0
        Application.CalculateFull
        
        ' 计算有RP的结果
        With wsOutput
            .Range("D7").Formula = "=SUMPRODUCT(" & wsModel.Name & "!BJ6:BJ365," & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365)"
            .Range("D8").Formula = "=SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BK6:BK365)"
            .Range("D10").Formula = "=SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BM6:BM365)"
            .Range("D11").Formula = "=SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BN6:BN365)"
            .Range("D12").Formula = "=SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BO6:BO365)"
            .Range("D13").Formula = "=SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BP6:BP365)"
            .Range("D14").Formula = "=SUM(" & .Name & "!D11:D13)"
            .Range("D17").Formula = "=-SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BS6:BS365)"
            .Range("D18").Formula = "=-SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BT6:BT365)"
            .Range("D19").Formula = "=-SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BU6:BU365)"
            .Range("D20").Formula = "=-SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BV6:BV365)"
            .Range("D21").Formula = "=-SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BW6:BW365)"
            .Range("D22").Formula = "=-SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BX6:BX365)"
            .Range("D23").Formula = "=-SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BY6:BY365)"
            .Range("D24").Formula = "=-SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!BZ6:BZ365)"
            .Range("D25").Formula = "=-SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!CA6:CA365)"
            .Range("D26").Formula = "=-SUMPRODUCT(" & wsModel.Name & "!AD6:AD365," & wsModel.Name & "!AG6:AG365," & wsModel.Name & "!CB6:CB365)"
            .Range("D5").Formula = "=" & wsModel.Name & "!BL6-" & wsModel.Name & "!BS6-" & wsModel.Name & "!BT6"
            .Range("D15").Formula = "=SUM(" & .Name & "!D7:D10," & .Name & "!D14)"
            .Range("D27").Formula = "=SUM(" & .Name & "!D17:D26)"
            .Range("D29").Formula = "=-SUM(" & wsModel.Name & "!AN6:AN365)"
            .Range("D30").Formula = "=-SUM(" & wsModel.Name & "!AP6:AP365)"
            .Range("D31").Formula = "=-" & .Name & "!C2"
            .Range("D33").Formula = "=SUM(" & .Name & "!D29:D31," & .Name & "!D27," & .Name & "!D15)"
        End With
        
        ' 转换为值，移除公式
        wsOutput.Range("D5:D33").Copy
        wsOutput.Range("D5:D33").PasteSpecial xlPasteValues
        
        ' 累加当前循环的结果到求和变量
        sumC33 = sumC33 + wsOutput.Range("C33").Value
        sumD33 = sumD33 + wsOutput.Range("D33").Value
        
        ' 递增行号，处理下一行
        i = i + 1
    Loop
    
    ' 循环结束后，把总和写入指定单元格（这里写入Output的E33和F33，可按需修改）
    wsOutput.Range("E33").Value = sumC33
    wsOutput.Range("F33").Value = sumD33
    
    ' 清除剪贴板，避免弹窗提示
    Application.CutCopyMode = False
End Sub

额外优化说明

用Worksheet对象替代重复调用Sheets()，不仅运行更快，还能避免工作表名称变化导致的错误
直接赋值替代Copy/Paste，减少内存占用，运行效率提升明显
循环前检查空单元格，确保不会处理无效行
求和变量用Double类型，适合处理数值计算，避免精度丢失

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

Excel VBA循环求和及空单元格终止循环问题求助

一、解决循环读取空单元格的问题 #

二、实现每次循环结果的求和 #

三、修改后的完整优化代码 #

额外优化说明 #

一、解决循环读取空单元格的问题

二、实现每次循环结果的求和

三、修改后的完整优化代码

额外优化说明