Picking numbers randomly from an existing list

[scouse]

Greetings,

i have been given 10 case numbers of which 4 need to picked randomly to ensure no bias. How can this be done through a button in excel?

The above is my main concern - but if possible.....
I am looking to make a button which will pick a number and display it, say cell A1 and then, the next time the button is hit, into, say, cell A2 (just so the button can be hit 4 times and the results shown)

regards and thanks in advance,
Scouse

 

[Norie]

Where are the 10 numbers located?

Are they on a worksheet?

 

[scouse]

I havent done anything yet - all i have been given is a list of the 10 numbers. I was just assuming that i would put them into a worksheet and then somehow randomly select them from the column that they are in,

 

[pcc]

put numbers in column A and then run this.
adapt to suit

Sub test_code()
ncell = 4
lr = [a1].CurrentRegion.Rows.Count

Columns(1).Font.ColorIndex = 1
Columns(1).Font.Bold = False

ReDim test(lr)
ReDim myarray(ncell)  ' this is number of cells to highlight
For r = 1 To lr  ' put cells values in array for now
test(r) = Cells(r, 1)
Next r


For iter = 1 To ncell   ' scan down the cells, pick largest number, remember the row number
mymin = 0
        For r = 2 To lr
            If Cells(r, 1) > mymin Then
            mymin = Cells(r, 1)
          myminrow = r
            End If
        Next r
        
myarray(iter) = myminrow    ' put row number(s) into myarray
Cells(myminrow, 1) = 0  ' set cell to zero so it's not picked again on next iteration

Next iter

For r = 1 To lr  ' put values back into cells
 Cells(r, 1) = test(r)
 For x = 1 To ncell
 If r = myarray(x) Then
 Cells(r, 1).Font.Bold = True  ' if the row was picked before, highlight it in bold
 Cells(r, 1).Font.ColorIndex = 3
 End If
 Next x
Next r
[a1].Select

End Sub

 

[Norie]

If the cases where in range A1:A10 something like this might work:

Sub Get4Random()
Dim X As Long
Dim I As Long
    I = 1
    Do
        
        X = CLng(Rnd * 10) + 1
        
        If Range("A" & X) <> "" Then
            Range("A" & X).Copy Range("B" & I)
            Range("A" & X).ClearContents
            I = I + 1
        End If
    Loop Until I = 4

End Sub

 

[scouse]

Thanks to you both - i will give them a go!
Would I attach these to a button?

 

[scouse]

Norie,
That works perfectly for what i need it for at the moment.
I am using your code attached to a button and it cuts out 4 numbers and puts them into col B. Is there anyway i can reset it once I have used it. At present if i use it, the second time i use it it puts four numbers on top of the others.
I have an idea that if i set it to, say, one random number it could have a wider use. e.g. ppl could click on it 9 or 10 times and get a random list in col B one from B1, B2....Bn

thanks again much appreciated,

Scouse

 

[Norie]

Scouse

I'm not 100% sure what you mean.

The following code should leave the list in column A intact:

Sub Get4Random()
Dim X As Long
Dim I As Long
Dim arrRange

    arrRange = Range("A1:A10")
    I = 1
    Do
        
        X = CLng(Rnd * 10) + 1
        
        If arrRange(X, 1) <> "" Then
            
            Range("B" & I) = arrRange(X, 1)
            arrRange(X, 1) = ""
            I = I + 1
        End If
    Loop Until I = 4

End Sub