Hi,

How can I generate 9 diffrent numbers from 1 to 9 thats not repeat it's self.
I need the number 1 - 9 in random order.

You can do it this way

put this formula in A1

=RAND()
and copy down to A9

in B1 use this formula

=RANK(A1,A$1:A$9)
copy down to B9

B1:B9 should now show 1-9 in random order, hide column A if desired.

Note: values will change every time sheet recalculates


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=506159

Try this it works but some on here may think its a bit poor?

Sub randNumber()

Range("A1").Value = Int((9 * Rnd) + 1)

For i = 1 To 8
rng = "A" & i
Redo:
Range("A1").Offset(i, 0).Value = Int((9 * Rnd) + 1)
For n = 0 To i - 1 Step 1
If Range("A1").Offset(i, 0).Value = Range("A1").Offset(n, 0) Then
n = n - 1
GoTo Redo
End If
Next n

Next i
End Sub

This puts a random number in cell A1 then starts a loop from A2 which first
starts by entering the next random number in A2 using offset then checks it
against A1 if it matches it enters another rand number until it finds one
that does not match. And then so on in the loop with the next cell.

Regards
Neil
"daddylonglegs"
wrote in message
news:daddylonglegs.22emnm_1138551900.9949@excelfor um-nospam.com...

You can do it this way

put this formula in A1

=RAND()
and copy down to A9

in B1 use this formula

=RANK(A1,A$1:A$9)
copy down to B9

B1:B9 should now show 1-9 in random order, hide column A if desired.

Note: values will change every time sheet recalculates


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=506159

For one way, see

http://www.mcgimpsey.com/excel/udfs/randint.html

In article ,
Roy wrote:

Hi,

How can I generate 9 diffrent numbers from 1 to 9 thats not repeat it's self.
I need the number 1 - 9 in random order.

Try this it works but some on here may think its a bit poor?

Sub randNumber()

Range("A1").Value = Int((9 * Rnd) + 1)

For i = 1 To 8
rng = "A" & i
Redo:
Range("A1").Offset(i, 0).Value = Int((9 * Rnd) + 1)
For n = 0 To i - 1 Step 1
If Range("A1").Offset(i, 0).Value = Range("A1").Offset(n, 0) Then
n = n - 1
GoTo Redo
End If
Next n

Next i
End Sub

This puts a random number in cell A1 then starts a loop from A2 which first
starts by entering the next random number in A2 using offset then checks it
against A1 if it matches it enters another rand number until it finds one
that does not match. And then so on in the loop with the next cell.

Regards
Neil
"Roy" wrote in message
...
Hi,

How can I generate 9 diffrent numbers from 1 to 9 thats not repeat it's
self.
I need the number 1 - 9 in random order.