Do...Loop whilst calling another macro

[samc2696]

Hi all,

I have a macro that generates a list of 27 random unique numbers from 1-90. From that, I have a weighted RAND function that gives me a 1 or a 0 beside the numbers in order to end up with a list of 15 unique numbers, which are then copied and pasted into a 9x3 table. Therefore, 15 of the table cells have unique values, and 12 of them are blank. Every time this macro runs the numbers get regenerated and new ones are copied in different positions (because of the weighted RAND.

I have two problems, but the second one may become null and void if someone has an answer to the first one. Whenever the macro runs the list of 1s or 0s recalculate like they should, but each time it gives me a different amount of 1s. Usually between 12 and 17 - I suspect its because the weighting of 15/27 is a recurring decimal (0.555...) but I am not sure. If someone knows how to always generate 15 randomly placed 1s then that would be amazing.

As I could not solve that, I thought about placing the macro in a Do...Loop. I have other macros I need to run at the same time, so I created an action button and placed a CALL to the unique number generator macro. The Do...Loop asks if the sum of the 1s and 0s is 15 (I have a SUM formula on the sheet for this in cell V29, and if not I want to regenerate the numbers until there are 15. However, the Do...Loop seems to end up looping infinitely and I can't figure out how the correct syntax to stop it from doing that.

Like I say if the first problem can be solved, it makes the second one irrelevant. Here is my attempt at the Do...Loop:

i = Worksheets("Sheet3").Range("V29").Value

Do Until i = 15

    Call TestUniqueRandomNumbers
    If i = 15 Then Exit Do
        
Loop
    
Call copy_paste_values

Any help really would be massively appreciated!
Sam

 

[mart37]

i = Worksheets("Sheet3").Range("V29").Value
Do Until i = 15
    Call TestUniqueRandomNumbers
    i = Worksheets("Sheet3").Range("V29").Value
Loop
Call copy_paste_values

 

[samc2696]

i = Worksheets("Sheet3").Range("V29").Value
Do Until i = 15
    Call TestUniqueRandomNumbers
    i = Worksheets("Sheet3").Range("V29").Value
Loop
Call copy_paste_values

Thank you for that - unfortunately, it still seems to be stuck in an infinite loop - and when I escape out of it highlights the 'End Sub' at the end of the TestUniqueRandomNumbers macro

 

[mart37]

I assume the macro TestUniqueRandomNumbers replace the 0 with 1 so that cell V29 is increased.
Can you check of cell V29 is changed after one loop?

 

[samc2696]

I assume the macro TestUniqueRandomNumbers replace the 0 with 1 so that cell V29 is increased.
Can you check of cell V29 is changed after one loop?

The macro running causes the workbook to recalculate thus triggering the RAND function to produce a new list of 1s and 0s

I stepped through the entire thing and after going round twice, V29 = 15 but the loop function ran again. So it must not be recognising that V29 =15 at that point.

 

[mart37]

With this macro you can test the value of i in the Window Direct (View).

i = Worksheets("Sheet3").Range("V29").Value
Do Until i = 15
    Call TestUniqueRandomNumbers
    i = Worksheets("Sheet3").Range("V29").Value
    Debug.Print i
Loop

 

[samc2696]

With this macro you can test the value of i in the Window Direct (View).

i = Worksheets("Sheet3").Range("V29").Value
Do Until i = 15
    Call TestUniqueRandomNumbers
    i = Worksheets("Sheet3").Range("V29").Value
    Debug.Print i
Loop

I realised I made a really big blunder, I had V29 instead of U29 as my reference... so it fully works but ONLY when I step through the code. If I click my action button it just runs like it did before as if its ignoring the loop until i=15

 

[mart37]

You have run the macro in #6 to control i ?
Maybe you need a timeout. I mean the macro.
Put this in the loop: Application.Wait (Now + TimeValue("0:00:5"))