random numbers with no repeats

[happyman123uk]

I want to create 8 numbers between 1 and 8 in 8 differant cells can this be done with no repeats ie 1,2,3,4,5,6,7,8 in a random order

 

[Jeff Meyers]

Fill-in A1:A8 with your sequence 1,2,3,4,5,6,7,8
Type =RAND() in B1 and copy to B2:B8
Sort both Columns based on Column B

 

[Trebor76]

If you'd like a macro approach, try this:

Option Explicit
Sub Macro1()
    
    'http://www.mrexcel.com/forum/showthread.php?t=623124

    Dim intRndVal As Integer
    Dim intLoopCounter As Integer, _
        intMinNum As Integer, _
        intMaxNum As Integer
    Dim blnNumberUsed() As Boolean
        
    intMinNum = 1 'Minimum number in draw.  Change to suit.
    intMaxNum = 8 'Maximum number in draw.  Change to suit.
    
    ReDim blnNumberUsed(intMinNum To intMaxNum)
    Randomize 'Without this, the numbers will always be displayed in the same order.
    
    intLoopCounter = 1 'Initialise variable
    Do While intLoopCounter <= intMaxNum
        intRndVal = Int(intMaxNum + 1 - intMinNum) * Rnd() + intMinNum
        If intRndVal >= intMinNum And intRndVal <= intMaxNum Then 'Ensure the correct number range is isued
            If blnNumberUsed(intRndVal) = False Then
                blnNumberUsed(intRndVal) = True
                Cells(intLoopCounter, "A").Value = intRndVal 'Outs the numbers from cell A[intLoopCounter]. Change to suit.
                intLoopCounter = intLoopCounter + 1
            End If
        End If
    Loop
    
End Sub

HTH

Robert

 

[happyman123uk]

useing a macro is no problem but what would i change to get it to start in say cell g7 down to g15

cheers

 

[Trebor76]

For an output range of G7:G14 i.e. 8 numbers, try this:

Option Explicit
Sub Macro2()
    
    'http://www.mrexcel.com/forum/showthread.php?t=623124
    
    Const conOutputCol As String = "G" 'Output column for the numbers. Change to suit.

    Dim intRndVal As Integer
    Dim intLoopCounter As Integer, _
        intMinNum As Integer, _
        intMaxNum As Integer
    Dim intOutputRow As Integer
    Dim blnNumberUsed() As Boolean
        
    intMinNum = 1 'Minimum number in draw.  Change to suit.
    intMaxNum = 8 'Maximum number in draw.  Change to suit.
    intOutputRow = 7 'Inital output row number for the numbers. Change to suit.
    
    ReDim blnNumberUsed(intMinNum To intMaxNum)
    Randomize 'Without this, the numbers will always be displayed in the same order.
    
    intLoopCounter = 1 'Initialise variable
    Do While intLoopCounter <= intMaxNum
        intRndVal = Int(intMaxNum + 1 - intMinNum) * Rnd() + intMinNum
        If intRndVal >= intMinNum And intRndVal <= intMaxNum Then 'Ensure the correct number range is isued
            If blnNumberUsed(intRndVal) = False Then
                blnNumberUsed(intRndVal) = True
                Cells(intOutputRow, conOutputCol).Value = intRndVal
                intLoopCounter = intLoopCounter + 1
                intOutputRow = intOutputRow + 1
            End If
        End If
    Loop
    
End Sub

Regards,

Robert

 

[Colin Legg]

I thought it would be worth quickly clarifying something in your thread title "random numbers with no repeats".

I want to create 8 numbers between 1 and 8 in 8 differant cells can this be done with no repeats ie 1,2,3,4,5,6,7,8 in a random order

In this case, the numbers themselves are not random: they are prechosen as 1 to 8. What is random is the order you want to put them in (as you mentioned at the end of your statement). This then suggests the methodology you should employ, be it by formulae or by VBA: preselect the numbers 1 to 8 and then shuffle them into a random order. This is what Jeff emulated in post #2.

 

[Boller]

Dim startCell As Range, rng As Range
Set startCell = [G7]
Set rng = startCell.Resize(8)
startCell(1, 2).EntireColumn.Insert
startCell = 1
startCell.AutoFill Destination:=rng, Type:=xlFillSeries
rng.Offset(0, 1) = "=RAND()"
rng.Resize(, 2).Sort Key1:=startCell(1, 2), _
    Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _
    MatchCase:=False, Orientation:=xlTopToBottom
startCell(1, 2).EntireColumn.Delete

 

[mirabeau]

Sub nonreprand()
Dim b(8) As Boolean, x As Long, c As Long
Do
x = Int(Rnd * 8) + 1
If Not b(x) Then
    c = c + 1
    Range("G7:G14").Cells(c) = x
    b(x) = True
End If
Loop Until c = 8
End Sub

 

[happyman123uk]

Thanks guys for all your help i am not good with programing gone with the macro's idea.Might need more help yet lol still loads to sort out but getting there

 

[happyman123uk]

What would i change to make it a random with a repeat?