Choose a random number from an array, then eliminate

[brumby]

dunno if i give this the right title.

What I am after is this :

I have a list of numbers ( 1-100 ), in column A.

I want to choose a random number from that list ( eg 59 ).

I then want to be able to show in an another column, numbers 1 to 100, but not showing number 59.

I want to repeat this 10 times, so each time, choosing a random number, then producing a list, but not showing the number previously selected.

Ive tried for days, but now i am totally beaten.

Many Thanks,

 

[Joe Was]

Sub buildLst()
'Builds 10 lists, on the active Sheet,each missing one random # each.
'Also displays a MsgBox listing the missing # in each list!

For myRI = 1 To 10

Randomize
'Note: The 100 below = the ending number of the data list.
' The 1 below = the starting number of your data list.
myRanItem = Int((100 * Rnd) + 1)
myItems = myItems & "List: " & myRI & " is missing: " & myRanItem & vbLf

For myDat = 1 To 100
If myDat <> myRanItem Then ActiveSheet.Cells(65536, myRI).End(xlUp).Offset(1, 0).Value = myDat
Next myDat

Next myRI
MsgBox myItems
End Sub

 

[erik.van.geit]

Hello,
this would be my approach
deleting the random item in each column

Sub test()
'Erik Van Geit
'051216
'data in column A
'copied data in column B, C, D ... "missing" one random item
Dim LR As Long
Dim I As Integer

Const FR As Integer = 1 'first row
Const NC As Integer = 5 'number of columns

LR = Cells(Rows.Count, 1).End(xlUp).Row

Randomize Timer

Range("A" & FR & ":A" & LR).Copy Range("B" & FR & ":B" & LR).Resize(LR - FR + 1, NC)

For I = 1 To NC
Cells(Int((LR - FR + 1) * Rnd + FR), I + 1).Delete shift:=xlUp
Next I

End Sub

kind regards,
Erik

 

[brumby]

many thanks,

Will this mean if a number is generated the same number cannot be generated in the next array?

 

[erik.van.geit]

many thanks,

Will this mean if a number is generated the same number cannot be generated in the next array?

would you like to avoid the same item deleted in different columns ?

if so, then have also a look at this thread for some tips

 

[erik.van.geit]

I edited cajones code from the other thread a bit...

see what this code does on an empty sheet
the resulting list could then be used in your project

Option Explicit

Sub pick_numbers()

Dim I As Long
Dim DR As Long  'row to delete

Const FN = 111  'first number
Const LN = 999  'last number
Const NR = 100  '# of items to pick

    If FN > LN Or NR > LN - FN + 1 Then
    MsgBox "Lowest Number < Highest Number" & Chr(10) & "# items < Highest Number - Lowest Number +1", 48, "ERROR"
    Exit Sub
    End If

Application.ScreenUpdating = False

Columns("A:B").ClearContents

    With [A1]
    .Value = 111
    .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=LN
    End With
Range("B1:B" & LN - FN + 1) = "=RAND()"
[A:B].Sort Key1:=[B1], Order1:=xlAscending, Header:=xlNo
[B:B].ClearContents
Range("A" & NR + 1 & ":A" & LN).Delete Shift:=xlUp

Application.ScreenUpdating = False

End Sub