Excel VBA Custom Randomizer
Results 1 to 8 of 8

Thread: Excel VBA Custom Randomizer
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2016
    Posts
    174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel VBA Custom Randomizer

    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.
    Last edited by MattH1; Jun 21st, 2019 at 10:10 PM.
    A 'LIKE' is the best way to show appreciation for the assistance another member provided.

  2. #2
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,381
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel VBA Custom Randomizer

    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?
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  3. #3
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,381
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Excel VBA Custom Randomizer

    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?? ....
    Code:
    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
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  4. #4
    Board Regular
    Join Date
    Jul 2016
    Posts
    174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Custom Randomizer

    Quote Originally Posted by lrobbo314 View Post
    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.
    A 'LIKE' is the best way to show appreciation for the assistance another member provided.

  5. #5
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,381
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Excel VBA Custom Randomizer

    Have you tried post#3 code?
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  6. #6
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,381
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel VBA Custom Randomizer

    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.

    Code:
    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
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  7. #7
    Board Regular
    Join Date
    Jul 2016
    Posts
    174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Custom Randomizer

    Quote Originally Posted by JoeMo View Post
    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?? ....
    Code:
    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.
    Last edited by MattH1; Jun 21st, 2019 at 11:24 PM.
    A 'LIKE' is the best way to show appreciation for the assistance another member provided.

  8. #8
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,381
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Excel VBA Custom Randomizer

    Quote Originally Posted by MattH1 View Post
    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?
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •