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

[Gary''s Student]

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

--
Gary's Student


"Lee Harris" wrote:

> 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
>
>
>

[Lee Harris]

"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> --
> Gary's Student


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

On Fri, 25 Nov 2005 01:11:09 GMT, "Lee Harris" <[email protected]> wrote:

>
>"Gary''s Student" <[email protected]> wrote in message
>news:[email protected]...
>> 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
>>
>> --
>> Gary's Student
>
>
>thanks, that looks great, I just wasn't sure what the syntax was though I
>guessed the approximate format!
>