Excel VBA run-time error 1004: Application-defined or object-defined error

1
votes

very novice user, having some difficulty getting this code up and running. I am attempting to calculate a value based on some other cells and iterating to minimize error. When running, I get the aforementioned error on the Cells(i, 17) = Cells(i, 5) line. Thoughts? Thank you.

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
2
excelvbaruntime-error
What are you trying to do when setting Cells(i, 17) = Cells(i, 5)? Set the value in the cell Cells(i,17) equal to the value currently in Cells(i, 5)? Also what is the For Loop doing? Right now it is going through the entire loop before getting to your iter variable, so the results of your for loop are going to be exactly the same each time. – tittaenälg

2 Answers

5
votes

When you exit that loop, the value of i is actually the amount of rows in the sheet +1 which is causing the error - you're trying to reference a row that doesn't exist.

If you want the last row in the sheet just use:

Dim i As Long
i = Rows.Count

Explanation:

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
2
votes

The problem here is that the for loop will have increased i beyond Rows.Count. That means that Cells(i, 17) = Cells(i, 5) is actually referring to the row immediately after the end of the spreadsheet.

Normally, we would not use i afterNext i. Normally, we only use the variable between the For and the Next. It's unclear to me what you expect theFor...Nextloop to achieve, because they usually are used to execute some code many times over, just changing a variable each time.

One thing you might find useful: You can step through the code line by line, using F8. Then you can inspect variables and other things to make sure they are as you're expecting.