Creating excel VBA macro that generates unique random numbers

Question

I want to create a macro that will essentially return random numbers based on a users input, however, I want each output to be unique (which is why the randbetween() function won't work for this). Below is what I have so far, but I keep getting a reference error. I have stitched this code together from a few different examples I found online so optimizing in any way would also be appreciated.

Code:

Sub RandomSample()
Dim cell As Range
Dim rng As Range
Low = 1
High = Application.InputBox("Enter population total", Type:=1)
Sample = Application.InputBox("Enter the Sample Size", Type:=8)
Set rng = Application.Range(ActiveCell, ActiveCell.Offset(Sample, 0))
For Each cell In rng.Cells
    If WorksheetFunction.CountA(Selection) = (High - Low + 1) Then Exit For
    Do
        rndNumber = Int((High - Low + 1) * Rnd() + Low)
    Loop Until Selection.Cells.Find(rndNumber, LookIn:=xlValues, lookat:=xlWhole) Is Nothing
    cell.Value = rndNumber
Next
End Sub

error window: Error image

Mrig · Accepted Answer · 2017-09-15 08:38:13Z

3

Try this

Sub RandomSample() Dim cell As Range Dim Sample As Range 'declare Sample as Range Low = 1 High = Application.InputBox("Enter population total", Type:=1) Set Sample = Application.InputBox("Enter the Sample Size", Type:=8) For Each cell In Sample 'use sample in loop If WorksheetFunction.CountA(Sample) = (High - Low + 1) Then Exit For Do rndnumber = Int((High - Low + 1) * Rnd() + Low) Loop Until Sample.Cells.Find(rndnumber, LookIn:=xlValues, lookat:=xlWhole) Is Nothing cell.Value = rndnumber Next End Sub

EDIT :

Sub RandomSample()
    Dim cell As Range
    Dim rng As Range
    Dim High As Long, Sample As Long
    Low = 1
    High = Application.InputBox("Enter population total", Type:=1)
    Sample = Application.InputBox("Enter the Sample Size", Type:=1)
    Set rng = Application.Range(ActiveCell, ActiveCell.Offset(Sample, 0))
    For Each cell In rng.Cells
        If WorksheetFunction.CountA(rng) = (High - Low + 1) Then Exit For
        Do
            rndNumber = Int((High - Low + 1) * Rnd() + Low)
        Loop Until rng.Cells.Find(rndNumber, LookIn:=xlValues, lookat:=xlWhole) Is Nothing
        cell.Value = rndNumber
    Next
End Sub

edited Sep 15, 2017 at 8:38

answered Sep 14, 2017 at 15:24

Mrig

11.7k2 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mrig Over a year ago

@Jeeped - Missed it, thanks for pointing it out, answer updated.

Scott Craner Over a year ago

you may also want to see the other answer about the type:= error.

Mrig Over a year ago

@ScottCraner - Seems like I went completely in wrong direction.

Melk Over a year ago

I was still getting the error with this code, kurtz suggestion of changing the Sample type worked for me.

Mrig Over a year ago

@Melk - I've added another piece of code, you might check.

|

Scott Craner · Accepted Answer · 2017-09-14 15:26:36Z

3

Here is a formula that does what you want:

=IF(ROW(1:1)<=$B$1,INDEX(ROW(INDIRECT("1:" & $A$1)),AGGREGATE(15,6,ROW(INDIRECT("1:" &$A$1))/(COUNTIF($A$2:A2,ROW(INDIRECT("1:" & $A$1)))=0),RANDBETWEEN(1,$A$1-COUNT($A$2:A2)))),"")

Where A1 is the population and B1 is the Sample size

answered Sep 14, 2017 at 15:26

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Comments

hod · Accepted Answer · 2017-09-14 15:28:16Z

2

Your Sample uses Type:=8 which is a Range and then you're trying to use it as a "number" in an "Offset" function. Change this line:

Sample = Application.InputBox("Enter the Sample Size", Type:=8)

To:

Sample = Application.InputBox("Enter the Sample Size", Type:=1)

answered Sep 14, 2017 at 15:28

hod

7619 silver badges22 bronze badges

1 Comment

user4039065 Over a year ago

This does not address the abstract use of Selection in the code.

Collectives™ on Stack Overflow

Creating excel VBA macro that generates unique random numbers

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related