Random Interger

G

Guest

Guest
I need to select 20 questions from a group of 100. It needs to be random and none repeating.

Any suggestions?

Thanks
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Enter =RAND() in a column (e.g., B) for each question. Use =RANK(B1,B:B) in a column (e.g., C) for each question. Filter on column C for ranks <=20.
 
Upvote 0
This program will give you 20 random integers between 1 through 100 in cells a1-a20. If you get a duplicate, run it again. Word to the wise, it's a little pokey, give her a minute or two:

Sub Ran()
Application.ScreenUpdating = False
[a1:a20].Select
For Each cell In Selection
ActiveCell = Int((100 * Rnd) + 1)
ActiveCell.Offset(1, 0).Select
Next cell
[a1].Select
Application.ScreenUpdating = True

End Sub


Cheers, Nate
This message was edited by NateO on 2002-03-14 11:08
 
Upvote 0
One, if so inclined, could layer on a second procedure to ensure uniqueness:

Code:
Option Explicit
Private upr As Integer
Private lwr As Integer

Sub Ran()
Dim cell As Range
upr = 100 'upper integer limit
lwr = 1   'lower integer limit
Application.ScreenUpdating = False
[a1:a20].Select
For Each cell In Selection
Randomize
ActiveCell = Int((upr - lwr + 1) * Rnd + lwr)
Call tstdup
ActiveCell.Offset(1, 0).Select
Next cell
[a1].Select
Application.ScreenUpdating = True
End Sub

Private Sub tstdup()
Dim LastRow As Integer
Dim c As Range
Dim myrng As Range
Dim SearchValue As String
LastRow = ActiveCell.Row - 1
If LastRow = 0 Then Exit Sub
Set myrng = Range("a1:a" & LastRow)
SearchValue = ActiveCell.Value
With Range("A1:A" & LastRow)
Set c = .Find(what:=SearchValue, LookIn:=xlValues, lookat:=xlWhole)
End With
If Not c Is Nothing Then
ActiveCell = Int((upr - lwr + 1) * Rnd + lwr)
Call tstdup
End If
Set myrng = Nothing
Set c = Nothing
End Sub

Cheers,
Superman_shield_small.GIF
<font size="+30"><big><font color="red">Nate<font color="blue">O</font></font></font></big>
This message was edited by NateO on 2002-03-14 15:03
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top