How easy is it to loop in VBA based on cell contents?
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 |
How easy is it to loop in VBA based on cell contents?
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 |
How easy is it to loop in VBA based on cell contents?
"Gary''s Student" wrote in message ... 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! |
How easy is it to loop in VBA based on cell contents?
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" wrote: "Gary''s Student" wrote in message ... 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! |
All times are GMT +1. The time now is 04:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com