Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do until loop with use of another macro in loop
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do until loop with use of another macro in loop
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do until loop with use of another macro in loop
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do until loop with use of another macro in loop
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do until loop with use of another macro in loop
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do until loop with use of another macro in loop
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do until loop with use of another macro in loop
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do until loop with use of another macro in loop
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do until loop with use of another macro in loop
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do until loop with use of another macro in loop
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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
For Each ... Next loop - need to reference the loop variable | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Loop Function unable to loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |