# Do...Loop whilst calling another macro

#### samc2696

##### New Member
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
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
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
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
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

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
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
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
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"))

Replies
0
Views
56
Replies
3
Views
81
Replies
1
Views
82
Replies
6
Views
185
Replies
8
Views
155

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.

### Which adblocker are you using?

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

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