Ending For/Next statement that gets stuck in a loop

[oravec]

Hello,

Following is part of I macro I am working on and it can occassionally get stuck in the For/Next statement below. I would like to know how to code it so that if it goes to error4 more that x (say 20) number of times, it will exit this loop and Go To error1.

Any Help appreciated. Cliff

Dim MyValue
Randomize
For c = 4 To 6: For d = 5 To 7
error4:
MyValue = Int((9 * Rnd) + 1) ' Generate random value between 1 and c.
If Cells(c, 2) = MyValue Or Cells(c, 3) = MyValue Or Cells(c, 4) = MyValue _
Or Cells(c, 5) = MyValue Or Cells(c, 6) = MyValue Or Cells(c, 7) = MyValue _
Or Cells(c, 8) = MyValue Or Cells(c, 9) = MyValue Or Cells(c, 10) = MyValue _
Or Cells(4, d) = MyValue Or Cells(5, d) = MyValue Or Cells(6, d) = MyValue _
Or Cells(7, d) = MyValue Or Cells(8, d) = MyValue Or Cells(9, d) = MyValue _
Or Cells(10, d) = MyValue Or Cells(11, d) = MyValue Or Cells(12, d) = MyValue _
Or Cells(4, 5) = MyValue Or Cells(4, 6) = MyValue Or Cells(4, 7) = MyValue _
Or Cells(5, 5) = MyValue Or Cells(5, 6) = MyValue Or Cells(5, 7) = MyValue _
Or Cells(6, 5) = MyValue Or Cells(6, 6) = MyValue Or Cells(6, 7) = MyValue Then
GoTo error4
End If
Cells(c, d) = MyValue
Next: Next

 

[BrianB]

Don't really know without seeing the other code.

The method would be to have a counter for the error, and re-route
eg. one way might be :-

'- UNTESTED
error4:
    If error4count = 20 Then
        GoTo error1
    Else
        error4count = error4count + 1
        '- error4 code here
        Resume Next ' (?) or whatever
    End If

 

[tusharm]

What are you trying to do? Using an embedded GOTO to simulate a loop is not a good idea!

FWIW, the statement generating a random number doesn't match the comment on the same line.

Also, from what I can tell you are trying to use VBA/Solver for that number puzzle that puts 1 to 9 in each row and each column. I don't believe the algorithm you've picked will work for it. There have been a few discussions on this subject. Search the forum for some options.

Hello,

Following is part of I macro I am working on and it can occassionally get stuck in the For/Next statement below. I would like to know how to code it so that if it goes to error4 more that x (say 20) number of times, it will exit this loop and Go To error1.

Any Help appreciated. Cliff

Dim MyValue
Randomize
For c = 4 To 6: For d = 5 To 7
error4:
MyValue = Int((9 * Rnd) + 1) ' Generate random value between 1 and c.
If Cells(c, 2) = MyValue Or Cells(c, 3) = MyValue Or Cells(c, 4) = MyValue _
Or Cells(c, 5) = MyValue Or Cells(c, 6) = MyValue Or Cells(c, 7) = MyValue _
Or Cells(c, 8) = MyValue Or Cells(c, 9) = MyValue Or Cells(c, 10) = MyValue _
Or Cells(4, d) = MyValue Or Cells(5, d) = MyValue Or Cells(6, d) = MyValue _
Or Cells(7, d) = MyValue Or Cells(8, d) = MyValue Or Cells(9, d) = MyValue _
Or Cells(10, d) = MyValue Or Cells(11, d) = MyValue Or Cells(12, d) = MyValue _
Or Cells(4, 5) = MyValue Or Cells(4, 6) = MyValue Or Cells(4, 7) = MyValue _
Or Cells(5, 5) = MyValue Or Cells(5, 6) = MyValue Or Cells(5, 7) = MyValue _
Or Cells(6, 5) = MyValue Or Cells(6, 6) = MyValue Or Cells(6, 7) = MyValue Then
GoTo error4
End If
Cells(c, d) = MyValue
Next: Next