Loop macro until cell displays text

[caricari]

Hi,
I've searched through 10 pages of macro looping on the forum in the hope of adapting one of the solutions to my problem.
Unfortunately, I have had no luck. My VBA skills are poorer than I thought and I'm thoroughly confused!

I'm trying to find a way for my macro to loop until either Yes or No appears in cell T6.
If anyone could offer a solution I'd be very grateful.

Thank you

Sub Com()

      If Range("Q4") = "SIMULATION" Then
    
        Application.Calculation = xlCalculationManual
        Application.ScreenUpdating = False
        
           Matches = Worksheets("Lists").Range("H36")
           If Worksheets("Trades Log").Cells(Matches + 3, 14) > 0 Then
           Matches = Matches + 1
      End If
        
            Worksheets("Lists").Range("H36") = Matches
            Range("B5") = 0
            Range("Q19:X41").ClearContents
            Range("P19:P41") = False
            Range("V8:W14").ClearContents
            Range("V3:X5").ClearContents
            Range("S8:t10").ClearContents
            Range("S13:T15").ClearContents
            
            Worksheets("Lists").Range("Q20") = Application.WorksheetFunction.RandBetween(1, 30000)
            Worksheets("Lists").Range("S16") = 0
            Worksheets("Lists").Range("S20") = 1
            
            Range("Q6").Formula = "=((AA4+AA6+AA8)/3)/100"
            Range("R6").Formula = "=AF4+AF6+AF8"
            Range("S6").Formula = "=IF(((AB4+AC4+AB6+AC6+AB8+AC8)/6/100)>(((AA4+AA6+AA8)/3)/100),AC12,"""")"
            Range("T6").Formula = "=IF(OR(AD4+AD6+AD8=3),""Yes"",IF(OR(AE4+AE6+AE8=3),""No"",""""))"
                            
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
        
    Else
    End If
    
End Sub

 

[Joe4]

Welcome to the Board!

I think we need to understand a little more about the data you are working with and what exactly you are trying to accomplish.

Where did you get this code that you are currently using?

Where should this loop go?
What exactly is it looping through?

If the rows at the end that populate row 6 are to be included in the loop, what is the point of that? They are just populating with formulas every single time, so there doesn't seem to be a need to re-do them every time.

If T6 is a formula (which it appears to be), you will probably need to turn calculations on again within the loop, or else it won;t ever recalculate the value and you will probably get stuck in an infinite loop.

 

[caricari]

Thank you for your reply Joe4.

Where did you get this code that you are currently using?
....The code comes from a spreadsheet used to calculate football outcomes.
I have modified it to meet my particular requirements.

Where should this loop go?
What exactly is it looping through?
......I don't know how to answer that because I don't understand what exactly you are asking.
To me, it is a macro that processes data when I run it and I have added the formulas at the end to give results that the original did not.
When the macro runs it simulates results but I want to skip the ones that I don't need i.e. when T6 displays 'Yes', that result meets my criteria. To get to that 'Yes' I may have to click the macro button dozens of times.


If the rows at the end that populate row 6 are to be included in the loop, what is the point of that?
They are just populating with formulas every single time, so there doesn't seem to be a need to re-do them every time.
.....When the macro runs, it displays data obtained from a table but it didn't tell me what I wanted to know. Adding the formulas at the end displays whether I want to examine its findings or pass and run the simulation again.

I really don't understand your last statement, sorry.

 

[Joe4]

Ok, the following code could probably be re-written more efficiently, if we had a fuller understanding of what your current data structure looks like and the purpose of each part of the code, but it should work nonetheless. I added an "out" so that it will give up after 1000 tries so that you do not get caught in an infinite loop:

Sub Com()

    Dim counter As Long
    Dim Matches
        
    If Range("Q4") = "SIMULATION" Then
 
 '      Start loop
        Do
 
            Application.Calculation = xlCalculationManual
            Application.ScreenUpdating = False
        
            Matches = Worksheets("Lists").Range("H36")
            If Worksheets("Trades Log").Cells(Matches + 3, 14) > 0 Then
                Matches = Matches + 1
            End If
        
            Worksheets("Lists").Range("H36") = Matches
            Range("B5") = 0
            Range("Q19:X41").ClearContents
            Range("P19:P41") = False
            Range("V8:W14").ClearContents
            Range("V3:X5").ClearContents
            Range("S8:t10").ClearContents
            Range("S13:T15").ClearContents
            
            Worksheets("Lists").Range("Q20") = Application.WorksheetFunction.RandBetween(1, 30000)
            Worksheets("Lists").Range("S16") = 0
            Worksheets("Lists").Range("S20") = 1
            
            Range("Q6").Formula = "=((AA4+AA6+AA8)/3)/100"
            Range("R6").Formula = "=AF4+AF6+AF8"
            Range("S6").Formula = "=IF(((AB4+AC4+AB6+AC6+AB8+AC8)/6/100)>(((AA4+AA6+AA8)/3)/100),AC12,"""")"
            Range("T6").Formula = "=IF(OR(AD4+AD6+AD8=3),""Yes"",IF(OR(AE4+AE6+AE8=3),""No"",""""))"
                            
            Application.Calculation = xlCalculationAutomatic
            Application.ScreenUpdating = True
            
'           Check value of T6 and exit if equal to "Yes" or "No"
            If Range("T6") = "Yes" Or Range("T6") = "No" Then Exit Do
            
'           Count loops
            counter = counter + 1
'           If over 1000 loops, exit loop (to prevent infinite loops)
            If counter > 1000 Then
                MsgBox "Ran 1000 times and gave up", vbOKOnly, "ERROR!"
                Exit Do
            End If
            
        Loop
    
    End If
            
 End Sub

 

[caricari]

Thank you, Joe4.
I ran your code and it works perfectly.
Exactly what I wanted.

Thank you for you time and expertise!

 

[Joe4]

You are welcome.
Glad it worked for you.