Excel VBA 如何将嵌套循环的计算结果逐行添加到表格新行中

问题原因 #

你代码中新增表格行的逻辑写在了循环外部，程序全程仅创建1行空行，所有循环的计算结果都反复覆盖这同一行的内容，最终仅保留最后一次循环的结果。

修复方案 #

将新增行的代码移动到嵌套循环的最内层，每次计算前先新增1行再写入对应值即可，修改后的完整代码如下：

Sub 计算结果写入表格()
    Dim SGF As Single
    Dim Y As Single
    Dim a As Single
    Dim b As Single
    Dim P As Single
    Dim Q As Single
    Dim ws As Worksheet
    Dim newrow As ListRow
    Set ws = ActiveSheet

    ' 可选：如果需要每次运行先清空表格原有数据，取消下面这行注释
    ' If Not ws.ListObjects("Table4").DataBodyRange Is Nothing Then ws.ListObjects("Table4").DataBodyRange.Delete

    P = Val(InputBox("Enter SG1 Value:"))
    Q = Val(InputBox("Enter SG2 Value:"))
    SGF = Val(InputBox("Enter SGF Value:"))

    For a = 1 To 10
        For b = 1 To 10
            Y = 0
            Y = Val((P * a) + (Q * b))
            ' 每次循环新增1行
            Set newrow = ws.ListObjects("Table4").ListRows.Add
            With newrow
                .Range(1) = a
                .Range(2) = b
                .Range(3) = Y
                .Range(4) = Val(SGF - Y)
            End With
        Next b
    Next a
End Sub

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