Excel VBA 运行时错误 1004 : Application-defined or object-defined error

Question

非常新手用户，在启动和运行此代码时遇到一些困难。我正在尝试根据其他一些单元格计算一个值并进行迭代以最小化错误。运行时，我在 Cells(i, 17) = Cells(i, 5) 行上收到上述错误。想法？谢谢你。

Private Sub CommandButton1_Click()
Dim i As Long
A = 6.112
B = 17.67
C = 243.5
epsilon = 0.622
G = B * C
maxerror = 0.001
For i = 2 To Rows.Count
Next i
iter = 0
Cells(i, 17) = Cells(i, 5)

    Do While iter < 50
        iter = iter + 1
        bt = B * Cells(i, 17)
        tpc = Cells(i, 17) + C
        d = (Cells(i, 9) / A) * Exp(-bt / tpc)
        dm1 = d - 1#
        f = (Cells(i, 5) - Cells(i, 17)) - Cells(i, 16) * (epsilon / dm1 - Cells(i, 13))
        df = -G / (tpc * tpc)
        df = d * df * Cells(i, 16) * epsilon / (dm1 * dm1) - 1#
        cor = f / df
        Cells(i, 17) = Cells(i, 5) - cor
        If Abs(cor) < maxerror Then
            Exit Do
        End If
    Loop


End Sub

Answer 1

当您退出该循环时，i 的值实际上是工作表中的行数 +1 这导致了错误 - 您正在尝试引用不存在的行。

如果您想要工作表中的最后一行，只需使用:

Dim i As Long
i = Rows.Count

解释:

For i = 1 To 10
    Debug.Print i '// Prints 1,2,3,4,5,6,7,8,9,10 as expected
Next i '// When i = 10 and gets to this line, it still
       '// increases the value before it exits the loop

Debug.Print i '// Prints 11