CODEWITHSUNDEEP
excelvbaexcel-formula
FTh
FTh

Reputation: 61

generate n random numbers not duplicated using vba

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?

Upvotes: 0

Views: 523

Answers (1)

AsUsual
AsUsual

Reputation: 524

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

Upvotes: 1

Related Questions