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.

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

"The Excelerator" wrote:

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

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.

--
Regards,
Tom Ogilvy


"The Excelerator" wrote:

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

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.

"Mike" wrote:

> 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
>
> "The Excelerator" wrote:
>
> > 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.

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

"Tom Ogilvy" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "The Excelerator" wrote:
>
> > 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.

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.

--
Regards,
Tom Ogilvy



"The Excelerator" wrote:

> 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
>
> "Tom Ogilvy" wrote:
>
> > 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.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "The Excelerator" wrote:
> >
> > > 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.

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

"Tom Ogilvy" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "The Excelerator" wrote:
>
> > 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
> >
> > "Tom Ogilvy" wrote:
> >
> > > 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.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "The Excelerator" wrote:
> > >
> > > > 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.

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


--
Regards,
Tom Ogilvy



"The Excelerator" wrote:

> 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
>
> "Tom Ogilvy" wrote:
>
> > 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.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "The Excelerator" wrote:
> >
> > > 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
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > 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.
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "The Excelerator" wrote:
> > > >
> > > > > 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.

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" wrote:

> Sub ABC()
> do while Range("A1").Value < 10
> RandomNumber
> Loop
> End Sub
>
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "The Excelerator" wrote:
>
> > 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
> >
> > "Tom Ogilvy" wrote:
> >
> > > 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.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > > "The Excelerator" wrote:
> > >
> > > > 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
> > > >
> > > > "Tom Ogilvy" wrote:
> > > >
> > > > > 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.
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > >
> > > > > "The Excelerator" wrote:
> > > > >
> > > > > > 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.

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

--
regards,
Tom Ogilvy


"The Excelerator" <(E-Mail Removed)> wrote in
message news:BD821F0F-9461-49DB-B682-(E-Mail Removed)...
> 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" wrote:
>
>> Sub ABC()
>> do while Range("A1").Value < 10
>> RandomNumber
>> Loop
>> End Sub
>>
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>>
>> "The Excelerator" wrote:
>>
>> > 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
>> >
>> > "Tom Ogilvy" wrote:
>> >
>> > > 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.
>> > >
>> > > --
>> > > Regards,
>> > > Tom Ogilvy
>> > >
>> > >
>> > >
>> > > "The Excelerator" wrote:
>> > >
>> > > > 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
>> > > >
>> > > > "Tom Ogilvy" wrote:
>> > > >
>> > > > > 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.
>> > > > >
>> > > > > --
>> > > > > Regards,
>> > > > > Tom Ogilvy
>> > > > >
>> > > > >
>> > > > > "The Excelerator" wrote:
>> > > > >
>> > > > > > 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.