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:
Any help really would be massively appreciated!
Sam
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