Randomize an order

tommade · Mar 28th 2016

I know how to ask excel to randomly placea number from 1-10 in a cell. How can I have excel do this for 10 cells but not repeat a number?

**alansidman** · Mar 29th 2016

Re: Randomize an order

This may work for you

Code

Option Explicit
Sub RandomNrs()
Range("A1:A10").ClearContents
Dim r As Long, c As Long, X As Long
For r =1 To 10
'For c = 1 to 2
10 X = WorksheetFunction.RandBetween(1, 10)
If WorksheetFunction.CountIf(Range("A1:A10"), X) >= 1 Then GoTo 10 'find another number
Cells(r, 1) = X
'Next c
Next r
End Sub

Display More

crushdrinker06 · Apr 6th 2016

Re: Randomize an order

I don't think this can be done with formulas in the cell without iterative calculations turned on. If you are willing to use iterative calculations then you can take a look at http://www.mrexcel.com/forum/e…-non-matching-number.html and play around with the formula posted by Marcelo Branco.

Share