Do until loop with use of another macro in loop

[Guest]

I am searching for some simple code to run a macro for a Do .. While loop,
where it continues to activate another macro event until a certain cell
reaches a predetermined count value.
For example ... lets say we run a rand number generator that copies the
generated diigt to a list (using an absolute copy paste to another location)
- under a separate macro (macro "RandomNumber") - and a cell (A1) counts the
number of 7's that appear. When it reaches the count of TEN appearances, then
the loop ceases and thus the macro RandomNumber" also ceases, until next
activated.

 

[Guest]

I don't know in your case how A1 gets to ten I assume you have a worksheet
formula to achieve this but the sub 'doforawhile' below will continually call
the second sub 'randomgenerato' until cell A1 on sheet1 = 10

Sub doforawhile()
Do
randomgenerator
Loop Until Worksheets("Sheet1").Cells(1, 1).Value = 10
End Sub

Sub randomgenerator()
myvalue = Int((10 * Rnd) + 1)
Worksheets("Sheet1").Cells(1, 1).Value = myvalue
End Sub

 

[Guest]

Sub ABC()
Range("A1").Formula ="=Countif(Sheet2!C:C,7)"
do while Range("A1") < 10
RandomNumber
Loop
End Sub

Assumes RandomNumber lists its output sequentially going down column C of
Sheet2. If not, then macro ABC would need to know where to look for the
results and maintain the count of 7's as well.

 

[Guest]

Thanks Mike. I'll give it a go and hopefully remember to post you a rating
and a thanks again. Just a note : cell A1 does have a worksheet formula
"countif" for all instances to get to that final count of ten.

 

[Guest]

Thanks Tom. I'll give it a go and hopefully remember to post you a rating
and a thanks again. Just a note : in this example you are correct in assuming
that its output sequentially going down column C.
When my other macros call up a 2nd macro, the auto written code within the
1st macro appears as :
Application.Run "RandomNumber"

Will my brand new macro need to have this call made as above or in your case
how does it know that RandomNumber is the macro to be called.

Sorry still learning (although I thought I was good at this)

JOHN

 

[Guest]

Sub ABC()
Range("A1").Formula ="=Countif(Sheet2!C:C,7)"
do while Range("A1") < 10
RandomNumber
Loop
End Sub

the statement randomnumber calls the macro randomnumber. Using
application.run would be the slowest way to call this routine.

Since you already have the coutnif formula in cell A1, you can delete that
line from the macro.

 

[Guest]

Thanks for the tip on calling a nmacro.
I'll try it now. Now finally if my countif is in cell A1, then is the code :
Range("A1").Formula
.....now

Thanks again

 

[Guest]

Sub ABC()
do while Range("A1").Value < 10
RandomNumber
Loop
End Sub

 

[Guest]

Tom the statement works very well thanks. Appreciate the help.

One other question now that I appreciate the simplicty of that code, earlier
you stated
Range("A1").Formula ="=Countif(Sheet2!C:C,7)" which makes use of the simple
countif formula. I may want to use it similarly for finding the Maximum
values in cells say, a1:g1 ..... so Range("A1").Formula ="=max(a1:g1)" would
be right ?
Where "Formula" denotes the use of a formula as found in excel's functions ??

Where could I find more understanding of other uses of this statement methods
Range("A1").Formula ="=Countif(Sheet2!C:C,7)"
Range("A1").Value < 10

in particular the understanding of Formula and Value in the above, as
initially I also wanted to get an easier statement than
Range("A1").Formula ="=max(a1:g1)"

Hope it all makes sense to you and I appologise for the use of your valuable
time.

JOHN

 

[Tom Ogilvy]

Range("A1").Formula ="=max(a1:g1)" would

be right ?

Yes.

If you want to see how a particular formula could be placed into a cell with
code, you can turn on the macro recorder and record while you put the
formula in manually. It might record the formula in R1C1 notation in later
versions of excel. Then you use FormulaR1C1.

Basically, you just enter the formula in the code like you would in a cell.
If the formula contains double quotes, you have to double the double quotes.
for example

=If(A1,"A","B")

would be entered as

Range("B9").Formula = "=If(A1,""A"",""B"")"

double quotes within a string must be doubled.