CODEWITHSUNDEEP
excelvba
Jimbo100
Jimbo100

Reputation: 5

Excel vba to count number of loops

I have a spreadsheet I've put together to randomly populate a list of judges against teams in a series of heats. The allocation of judges to teams is dependent on a number of criteria. For it to work correctly, it incrementally allocates random numbers to each judging pair, looping through the code until all criteria are met. The problem is that sometimes, depending on the initial allocation of numbers, it will never get to a point where all criteria are met, so just keeps looping. I've looked all over, but can't find anything which will work - does anyone have code which will count the number of times the code loops and if it reaches a threshold of say 100 loops it starts the original code all over again? I've not included any code in this post because the randomiser I have is really too big and not really relevant to this question, and I've been nowhere near successful with a loop counter. Given the expertise on this forum, I suspect the answer will be fairly easy, and a "doh!" moment on my part.

Upvotes: 0

Views: 2587

Answers (1)

cssyphus
cssyphus

Reputation: 40038

Quick and dirty, perhaps something like:

Dim maxcnt as Integer
Dim alldone as Boolean
alldone = False
Do While Not alldone
    For Each Team in AllTeams
        maxcnt = maxcnt + 1
        If maxcnt > 100 Then Exit For
        '==============================
        ' Your code logic
        ' goes here, and when
        ' all done, does this:
        alldone = True
        If alldone Then Exit For
        '==============================
    Next
    If alldone And maxcnt < 100 Then Exit Do
Loop
MsgBox "Job Completed"

You can probably improve this considerably, but off-the-cuff...

Upvotes: 1

Related Questions