do while error

[JPCAPL]

I can do a simple do while loop

Dim i as Integer
Do While i < 102
  i = i + 1
Loop
MsgBox "done"

When I add a cell value based on "i," I get an error

Do While i < 102
  Worksheets("Sheet1").Cells(i, 12).Value = 3
  i = i + 1
Loop
MsgBox "done"

Why, and how can I get around it?

 

[Richard Schollar]

Have you initialised i? You don't have a row 0 if not

i = 1
Do While i < 102
  Worksheets("Sheet1").Cells(i, 12).Value = 3
  i = i + 1
Loop
MsgBox "done"

 

[JPCAPL]

Yes, I do have my i initialized to i = 1. It is giving me "Code execution has been interrupted," and it highlights i = i + 1.

 

[Richard Schollar]

Try placing this at the top of your routine and then try running it again:

application.EnableCancelKey = xlDisabled

 

[JPCAPL]

That worked. Big thanks. I would have never, ever, ever got that.

 

[Scott Huish]

This can be done without a loop, but if you are going to use a loop, why not a For Next Loop?

Sub test()
Dim i As Integer
For i = 1 To 102
    Worksheets("Sheet1").Cells(i, 12).Value = 3
Next i
End Sub

This however will do the same thing:

Sub test2()
Range("L1:L102") = 3
End Sub

 

[JPCAPL]

I broke my message down to the essentials. I am actually trying to mine information from two columns of data. There are positive and negative values as a function of time. I am trying to gather all of the ranges of negative values. For each range I am going extract the Max and Min value, and the time in that range. I am close. I spent hours on this one error, so putting it behind me is a big help. I am sure there is a more elagant way to do this, but it has been a while, so I am just pounding it out with nested do's, for's, and if's.