getting excell to pick a number from 1 to 10

[tonylpcs@yahoo.co.uk]

Hi I think i have been looking at this a little wrong so maybe someone can help me?

i want excel to pick numbers from 1 to 10 in ten different rows but not numbers that have been picked before.

its a bit like setting up a ramdom lottery number picker in the sence that you want the numbers unique

so cells

a10 to a20 need to be a number between 1 and 10
then
cells b10 to b20 need to be numbers between 1 and 10 but excluding the number in the matching cell of A (I.E B2 cant equal A2 but any of the other numbers is fine,
then
cell C10 to C20 need to be numbers between 1 and 10 but not whats in cells a or b and so on.

any ideas?

thanks

Tony

 

[Joneye]

Try reading this, may help.

http://www.techonthenet.com/excel/formulas/rand.php

 

[Jaafar Tribak]

See if you can adapt this random generator routine :

Private Sub GenerateUniqueNumbers _
(ByVal Min As Byte, ByVal Max As Byte, ByRef ar() As Variant)

    Dim bCounter As Byte
    Dim bRndValue As Byte
    
    bCounter = 1
    
    Do
        On Error Resume Next
            Randomize
            With Application.WorksheetFunction
                bRndValue = .RandBetween(Min, Max)
                Call .Match(bRndValue, ar, 0)
            End With
            If Err.Number <> 0 Then
                ar(bCounter) = bRndValue: bCounter = bCounter + 1
            End If
        Err.Clear
        DoEvents
    Loop Until ar(UBound(ar)) <> Empty

End Sub

The following will use the above routine to put unique random numbers between 1-10 in the Range("A1:J1") :

Sub Test()

    Dim RandomValues(1 To 10)

    GenerateUniqueNumbers 1, 10, RandomValues
    Range("a1:j1") = RandomValues

End Sub

 

[sezuh]

I do not know how to do it in excel or vb code but i done it manually
here is the result;

Excel Workbook
	B	C	D
10	1	7	2
11	2	10	6
12	3	1	10
13	4	6	1
14	5	2	8
15	6	3	9
16	7	8	3
17	8	9	4
18	9	5	7
19	10	4	5
Sheet1

Is this what you want?