CODEWITHSUNDEEP
excelrandomvba
Thecatsloose
Thecatsloose

Reputation: 13

VBA =RANDBETWEEN

I hope you can help me figure out what I am unable to do myself. I work as an Auditor with large data sets and want to make taking samples easier for myself and everyone I work with.

I am trying to set up a random number generator using =RANDBETWEEN

What I would like to do is specify a field in which I type the range i.e. (1,20) and then for =RANDBETWEEN to pick that value up and generate numbers, but WITHOUT repeating any.

I bet there is a way, I just haven't considered it most likely and have a blonde moment.

Any help would be really appreciated.

Thanks

Upvotes: 1

Views: 1714

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

Building on what was found HERE

For a formula answer you will need two columns of formulas and an individual cell for the starting number and another cell for the ending number.

I used E1 for the starting number and F1 for the ending number.

Then in A1 I put the following:

=IF(ROW()<=$F$1-$E$1+1,RAND(),"")

And in C1 I put:

=IFERROR(MATCH(AGGREGATE(14,6,$A:$A,ROW(1:1)),$A:$A,0)+$E$1-1,"")

Then I copied both A1 and C1 down enough rows to cover the largest possible range I would want.

enter image description here

As you can see I wanted random numbers between 5 and 20. In column C I got 16 numbers from 5 to 20 in random order.

If I change the range from 6 to 15 it changes:

enter image description here

One note: The Aggregate Function was introduced in Excel 2010. So this will not work for earlier editions.

If you are using an earlier edition use this in C1:

=IF(A1<>"",RANK(A1,A:A)+$E$1-1,"")

And copy down.

As for a vba version:

Sub RndmUnquArr()
Dim ws As Worksheet
Dim mnm&, mxm&
Dim oArr() As Variant
Dim i&, j&, x&, temp&

Set ws = Sheets("Sheet1")
With ws
    mnm = .Range("E1") 'change as desired
    mxm = .Range("F1") 'change as desired

    ReDim tArr(0 To (mxm - mnm)) As Variant
    ReDim oArr(mnm To mxm) As Variant

    'Load array with integers from mnm to mxm
    For i = mnm To mxm
        oArr(i) = i
    Next i

    'Randomly suffle arr
    x = UBound(oArr)
    For j = LBound(oArr) To UBound(oArr)

        i = Int((x - mnm + 1) * Rnd + mnm)
        temp = oArr(i)
        oArr(i) = oArr(x)
        oArr(x) = temp

        x = x - 1
    Next j

    .Range("A1").Resize(mxm - mnm + 1).Value = Application.Transpose(oArr)
    ws.Calculate
End With

End Sub

Again the lower extant is in E1 and the upper is in F1

Upvotes: 1

Related Questions