Do...Loop whilst calling another macro

samc2696

New Member
Joined
Jul 16, 2018
Messages
42
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:

VBA Code:
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
 

Attachments

  • Picture3.jpg
    Picture3.jpg
    53.6 KB · Views: 2

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
i = Worksheets("Sheet3").Range("V29").Value
Do Until i = 15
    Call TestUniqueRandomNumbers
    i = Worksheets("Sheet3").Range("V29").Value
Loop
Call copy_paste_values
 

samc2696

New Member
Joined
Jul 16, 2018
Messages
42
VBA Code:
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

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jul 16, 2018
Messages
42

ADVERTISEMENT

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

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
With this macro you can test the value of i in the Window Direct (View).
VBA Code:
i = Worksheets("Sheet3").Range("V29").Value
Do Until i = 15
    Call TestUniqueRandomNumbers
    i = Worksheets("Sheet3").Range("V29").Value
    Debug.Print i
Loop
 

samc2696

New Member
Joined
Jul 16, 2018
Messages
42
With this macro you can test the value of i in the Window Direct (View).
VBA Code:
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

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
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"))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,924
Messages
5,639,025
Members
417,064
Latest member
oryngirl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top