Excel VBA Custom Randomizer

[MattH1]

Hi All,

I have a macro I'm working on and am having trouble creating a randomizer to find the answer to a solution I'm looking for. Here's the data I need to randomize:

Column A contains a Unique ID (1,2,3,4,5 etc)
Column B needs to be populated with a number between 1 and 50 but must be divisible by 5.

I'm looking to have Excel stop when a set of criteria is hit as it continues to randomize to find what I'm looking for.

e.g. Stop when the Unique IDs all have a value of 10 in Column B and output how many runs it took.

Please let me know if this is possible via VBA or if I need to work in Python/another coding language to solve for this.

Thanks.

 

[lrobbo314]

So, if a random number is generated and it is not divisible by 5, does that count as an iteration?

So far I have a loop that generates a number, 1-50, and essentially, if that number = 10, then it gets stored. And in each iteration of the loop, a counter is incremented. What confuses me is the divisible by 5 part. Do you increment on each loop, or only if the random number is divisible by 5?

 

[JoeMo]

Assuming your Unique IDs are already in A2:A?? and you run the code below, does it produce the result you want in B2:B?? ....

Sub MattH1()
Dim R As Range, loopCtr As Long, c As Range
Set R = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row)
Application.ScreenUpdating = False
R.Columns(2).ClearContents
For Each c In R.Columns(2).Cells
Again:  c.Value = Application.RandBetween(1, 50)
    loopCtr = loopCtr + 1
    If c.Value Mod 5 <> 0 Then GoTo Again
Next c
Application.ScreenUpdating = True
MsgBox "Took " & loopCtr & " runs to complete column B"
End Sub

 

[MattH1]

So, if a random number is generated and it is not divisible by 5, does that count as an iteration?

So far I have a loop that generates a number, 1-50, and essentially, if that number = 10, then it gets stored. And in each iteration of the loop, a counter is incremented. What confuses me is the divisible by 5 part. Do you increment on each loop, or only if the random number is divisible by 5?

Hi Robbo,

Appreciate you giving this an attempt. I do not want to run through the rest of my checks if the number is not divisible by 5. If it is divisible by 5, output that number in the cell and go onto the next cell. Continue doing this essentially throughout the file.

I'm scaling this to 1000+ rows where I have the unique ID and need to put a random number. I think my best bet is creating a code to loop through each row and do a random number and have it continue randomizing until it meets all criteria. IF it does, output that number and move to the next row.

Let me know if this helped or confused you more, apologize if it confused you.

 

[JoeMo]

Have you tried post#3 code?

 

[lrobbo314]

For the sake of speed, I am not outputting the results to the cells on each iteration. I've commented the code to explain it better. Let me know if this works.

Sub Randomizer()
Randomize
Dim R As Range: Set R = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")
Dim cnt As Integer: cnt = 0


Do Until AL.Count = R.Cells.Count 'Do until arraylist has the same number of items as the number of Unique IDs
    rSel = Int((50) * Rnd() + 1) 'Random number generated
    If rSel = 10 Then AL.Add 10 'if random number = 10, then add it to the array
    'if rsel mod 5 = 0 then cnt = cnt + 1 'uncomment this line if you only want the count to go up if the random number is divisible by 5
    cnt = cnt + 1 'If the divisible by 5 thing doesn't matter, just leave the code the way it is
Loop


R.Offset(, 1).Value = Application.Transpose(AL.toarray) 'Output to column B


MsgBox cnt & " Iterations"


End Sub

 

[MattH1]

Assuming your Unique IDs are already in A2:A?? and you run the code below, does it produce the result you want in B2:B?? ....

Sub MattH1()
Dim R As Range, loopCtr As Long, c As Range
Set R = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row)
Application.ScreenUpdating = False
R.Columns(2).ClearContents
For Each c In R.Columns(2).Cells
Again:  c.Value = Application.RandBetween(1, 50)
    loopCtr = loopCtr + 1
    If c.Value Mod 5 <> 0 Then GoTo Again
Next c
Application.ScreenUpdating = True
MsgBox "Took " & loopCtr & " runs to complete column B"
End Sub

Hi Joe, this seems to be working. I'm looking to upgrade it now to really capture all the criteria I'm looking for. I want to make the RandBetween range dependent upon a formula in Column X. e.g. First RandBetween is (1,500) but second is (1,400) because RandBetween delivered 100 and now the SUMIF difference only allows that row to go up to 400.

Is that something possible? I basically want it to know that because it's going down the list and the SUMIF formula is updating, the bounds will shrink.

robbo I open this up to you as well I just don't know that your methodology allows us to check it in each row as the code goes through it. Your coding makes sense to increase speed (which will be necessary here....), I just fear it won't allow for this addition above.

Thanks.

 

[JoeMo]

Hi Joe, this seems to be working. I'm looking to upgrade it now to really capture all the criteria I'm looking for. I want to make the RandBetween range dependent upon a formula in Column X. e.g. First RandBetween is (1,500) but second is (1,400) because RandBetween delivered 100 and now the SUMIF difference only allows that row to go up to 400.

Is that something possible? I basically want it to know that because it's going down the list and the SUMIF formula is updating, the bounds will shrink.

Thanks.

Maybe possible, but need some more info to assess. What's the formula in col X and how exactly is it to be used as a criterion? Can you provide an example of what a starting data set looks like and what you would consider the "solution" to look like after execution of the desired macro?