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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lee Harris - 24 Nov 2005 19:33 GMT

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

Reply to this Message

Gary''s Student - 24 Nov 2005 22:44 GMT

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

Signature

Gary's Student

> I have a function in code that I added via the built in form designer
>
[quoted text clipped - 25 lines]
> While Rules$a$1 AND iter < N
> End Sub

Reply to this Message

Lee Harris - 25 Nov 2005 01:11 GMT

> How about:
>
[quoted text clipped - 8 lines]
> Next
> End Sub

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

Reply to this Message

Gord Dibben - 25 Nov 2005 01:21 GMT

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

>> How about:
>>
[quoted text clipped - 11 lines]
>thanks, that looks great, I just wasn't sure what the syntax was though I
>guessed the approximate format!

Reply to this Message