VBA Code with Excel 2007 and RANDBETWEEN

  frager 16:34 21 May 08

I need to put the numbers 1 to 36 in a random order in column A from A1 to A36. I tried the RANDBETWEEN function, but I got repetition. I searched the ‘net and found a VBA program by shg ozmvp in click here. I pasted this code into my spreadsheet, but I can’t get it to work. I know very little about VBA and would appreciate any advice if what I’m asking will not take up too much of your time. I’m using Excel 2007. Thanks.

  VoG II 16:47 21 May 08

This will do what you want (and more).

Sub rdm()
Dim cell As Range, MyRanRng, x, nDec As Integer
Dim K As Long, iFlag As Boolean
Dim i As Integer, j As Integer, n As Integer
Dim NosAvailable As Long
Dim ArrayOfValues
MyRanRng = Application.InputBox(prompt:="Enter lower and upper limits separated by space", _
Title:="Enter number range")
If TypeName(MyRanRng) = "Boolean" Then Exit Sub
x = Split(MyRanRng)
If Not IsArray(x) Then Exit Sub
nDec = Application.InputBox(prompt:="No. decimal places", Title:="Enter decimal places", Type:=1)
If TypeName(nDec) = "Boolean" Then nDec = 0
K = Selection.Cells.Count
NosAvailable = (x(UBound(x)) - x(LBound(x))) * 10 ^ nDec + 1
If K > NosAvailable Then
MsgBox prompt:="Cells available:" & vbTab & K & vbCrLf & "Numbers available:" & _
vbTab & NosAvailable & vbCrLf & vbCrLf & _
"Select a smaller range or increase the number range", _
Title:="Error trap!", Buttons:=vbOKOnly + vbCritical
Exit Sub
End If
ReDim ArrayOfValues(1 To K) As Variant
For i = 1 To K
iFlag = False
ArrayOfValues(i) = Round(Rnd() * (x(UBound(x)) - x(LBound(x))) + x(LBound(x)), nDec)
For n = 1 To i - 1
If ArrayOfValues(i) = ArrayOfValues(n) Then iFlag = True
Next n
Loop Until iFlag = False
Next i
j = 0
For Each cell In Selection
j = j + 1
cell.Value = ArrayOfValues(j)
Next cell
End Sub

To use this press ALT + F11 to open the Visual Basic Editor then Insert > Module. Copy the above code into the white space on the right then close the VBE using the X.

Make sure that the Developer Tab is visible by clicking the Office icon (top left) > Excel Options and tick Show the Developer tab in the ribbon.

Select A1:A36 then on the Developer tab Click Macros, highlight rdm then click the Run button. Follow the on-screen prompts.

  frager 17:46 21 May 08

Thanks for your very fast response. It may be some time before I can get around to trying it. I'll let you know how I get on.

  Picklefactory 09:34 22 May 08


  frager 15:45 07 Jun 08

For VoG.
Apologies for not getting back to you before now.
Your code worked perfectly, as I knew it would. It did the job I wanted it to do. Thanks.

I have one further question:

Can it be edited to run the simulation a set number of times without having to input the lower and upper limits and the number of decimal places each time?

  VoG II 15:35 08 Jun 08

In principle it would be possible but why not run it once then use ASAP click here to shuffle the values randomly.

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Here's what should be coming to Adobe Project Felix in 2017

Apple AirPods review: Apple's beautiful new Bluetooth headphones bring true intelligence to…