1

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
Improve this question
1
  • 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. Commented Sep 2, 2016 at 14:45

2 Answers 2

Reset to default
5

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Good catch and explanation on just how i it incremented in a For ... Next.
2

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.