How easy is it to loop in VBA based on cell contents?

[Lee Harris]

I have a function in code that I added via the built in form designer

Option Explicit
Private Sub CommandButton1_Click()
Application.Calculate
End Sub

I access that with Alt-F11


How easy is it for me to change this such that after the relevant
Application.Calculate, it looks at the value in a specific cell on a
specific sheet, and if it's 1, then do Application.Calculate again, up to a
maximum of N times


I'm just not sure of the syntax when referring to cells etc

My application selects a football play from a list based on the game
situation, but there are specific situations where certain plays can't be
called. It would be a pain to code that in to the randomising on the sheets,
so what I'm trying to do essentially in quasi-code is


Private Sub ClickEventHandler()

iter=0

Do Application.Calculate
iter++
While Rules$a$1 AND iter < N
End Sub

 

[Guest]

How about:

Sub Macro1()
For i = 1 To 37
j = Sheets("Sheet3").Range("A1").Value
If j = 1 Then
Exit For
Else
Application.Calculate
End If
Next
End Sub

 

[Lee Harris]

How about:

Sub Macro1()
For i = 1 To 37
j = Sheets("Sheet3").Range("A1").Value
If j = 1 Then
Exit For
Else
Application.Calculate
End If
Next
End Sub

thanks, that looks great, I just wasn't sure what the syntax was though I
guessed the approximate format!

 

[Gord Dibben]

Shouldn't it read?

If J <> 1 Then

You wanted to iterate if A1 was 1 and not if something else.


Gord Dibben Excel MVP