Generating random question numbers for surveys or exams
I was recently asked to create a random question number generator in Microsoft Excel. The requirements were to create random question numbers from 1 to 65 that did not repeat. Here is the solution I came up with but I would interested to hear of any better methods:
Sub GenerateQuestions()
' Use Column B to display the random question numbers
Range("B1").Select
'Number of questions
questionCount = 65
For counter = 1 To questionCount
' Generate random number
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(1,65)"
questionsAllocated = counter - 1
' Check if question number already allocated
For checkCounter = 1 To questionsAllocated
If ActiveCell.Value = ActiveCell.Offset(-checkCounter, 0).Value Then
ActiveCell.Value = ActiveCell.Value + 1
If ActiveCell.Value = questionCount + 1 Then ActiveCell.Value = 1
checkCounter = 0
End If
Next checkCounter
' Copy and Paste Values so that allocated question numbers don't regenerate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' Go to next question
ActiveCell.Offset(1, 0).Select
Next counter 'Loop
End Sub