Sub RandomNoPicker()
' Author: David Zemens, Stackoverflow.com user
' Co-Author: Colin Delane, CA, Financial Analyst, Perth, Western Australia
' Source: Code adapted from https://stackoverflow.com/questions/35921217/excel-vba-to-get-random-integer-values-without-repetitions
' Purpose: Solution to issue raised by Millrobin on MrExcel Forum 27/02/2019 (to pick 52 random numbers in range from 1-200)
'https://www.mrexcel.com/forum/excel-questions/1089062-format-multiple-choice-test-questions-4-possible-answers.html#post5235145
' Pre-requisites:
' WARNINGS:
'---------------------------------------------------------------------------------------
'Declare Procedure Variables
Dim i As Integer, num As Integer
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim picked As Object 'Scripting.Dictionary
Dim val As Variant
Set rng1 = Range("TotalNoQtns")
Set rng2 = Range("RandomQtnsNo")
Set rng3 = Range("QtnNos")
Set rng4 = Range("RandSelection")
'Clear the ranges of any previous entries
rng3.ClearContents
rng4.ClearContents
'Populate the sheet with question numbers
For i = 1 To rng1.Value
rng3.Cells(i) = i
Next
'Store which numbers have been already chosen
Set picked = CreateObject("Scripting.Dictionary")
'Select random numbers:
i = 1
While picked.Count < rng2.Value
num = Application.WorksheetFunction.RandBetween(1, rng1.Value)
If Not picked.Exists(num) Then
picked.Add num, i
i = i + 1
End If
Wend
'Now, identify those numbers on the sheet
For Each val In picked.Keys()
rng3.Cells(val).Offset(0, 1).Value = "Y"
Next
MsgBox "Done!"
End Sub