generate n random numbers not duplicated using vba

Question

I'm trying to generate n random numbers not duplicated except this numbers ( 81-82-83-84-85-86).

In this VBA code b(1 to 80) I want to do for b(1 to 200) with no repeated numbers ( 81-82-83-84-85-86) and the number_required is the number of rows in one column every time I changed it:

Dim b() As Boolean, Bidder_ID As Range, k&, x&
ReDim b(1 To 80)
Dim destination_Bidder_ID As String
Randomize
Number_required = Range("K2").Value
destination_Bidder_ID = Range("L3").Value
Range(destination_Bidder_ID).Select

For Each Bidder_ID In Range(Selection, Selection.Offset(Number_required - 1, 0))
Do
    x = Int(Rnd() * 80) + 1
    If b(x) = False Then
        Bidder_ID.Value = x
        b(x) = True
        Exit Do
    End If
    k = k + 1: If k > 100 Then Exit Sub
Loop

Next

How can I generate random numbers from 1 to 200 except this numbers ( 81-82-83-84-85-86 ) that already exist within a defined range?

How can I generate random numbers from 1 to 200 except this numbers ( 81-82-83-84-85-86 )? i didnt know how to do it ...? — FTh
– FTh, Commented Dec 9, 2019 at 7:44
The first thing you will want to add is a Randomize statement right after you start the loop. — braX
– braX, Commented Dec 9, 2019 at 7:46
Yup - that may return the same value without it. That will be a problem. — braX
– braX, Commented Dec 9, 2019 at 7:50

AsUsual · Accepted Answer · 2019-12-09 13:50:26Z

1

Try this

Sub RndBtwn_Excldng()
    Application.ScreenUpdating = False

    Dim c As Range, rng As Range, i As Integer

    Set rng = Range([L3]).Resize([K2])

    For Each c In rng
        Select Case Int(rnd * 10) Mod 2
            Case 0: lb = 1: ub = 80
            Case 1: lb = 87: ub = 200
        End Select

        i = Int((ub - lb + 1) * rnd + lb)
        Do While Application.WorksheetFunction.CountIf(rng, i) >= 1
            i = Int((ub - lb + 1) * rnd + lb)
        Loop

        c = i
    Next
    Application.ScreenUpdating = True
End Sub

answered Dec 9, 2019 at 13:50

AsUsual

5342 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FTh Over a year ago

if I want to to run this code for each same id, I mean if id_number = id_number+1 generate random numbers not duplicated for them.. I put this condition but it hangs up If Cells(i, 1).Value = Cells(i+1,1) Then can you help me for that? @AsUsual

Collectives™ on Stack Overflow

generate n random numbers not duplicated using vba

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related