samedi 21 janvier 2023

Modify this sub random number generator sub to exclude certain numbers

I'm new to Excel VBA. The project I'm working on deals with ranges of random numbers. I have five ranges and found this code which works really well for not getting any duplicates in a range:

Public Sub generateRandNum()
    'Define your variabiles
    lowerbound = 1
    upperbound = 20000
    Set randomrange = Range("A1:C5000")
    
    randomrange.Clear
    For Each rng1 In randomrange
        counter = counter + 1
    Next
    
    If counter > upperbound - lowerbound + 1 Then
        MsgBox ("Number of cells > number of unique random numbers")
        Exit Sub
    End If
    
    For Each Rng In randomrange
        randnum = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
        Do While Application.WorksheetFunction.CountIf(randomrange, randnum) >= 1
            randnum = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
        Loop
        Rng.Value = randnum
    Next
End Sub

The next part of the project involves the excluding one number (not random) from the second set and two numbers (also not random) from the fourth set.

I've searched all over Google, looked at a number of forums, but either the code looks really long or I can't quite understand it enough to modify it for my needs.

It has to be in VBA because the number generator works off of a button click.




Aucun commentaire:

Enregistrer un commentaire