VBA Code with Excel 2007 and RANDBETWEEN

  frager 16:34 21 May 2008

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 2008

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 2008

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 2008


  frager 15:45 07 Jun 2008

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 2008

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.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment regarder des séries et talk-shows américains en France ?