generating random numbers without repeats

[Lynton]

In a1:a20 I have numbers 1-20. I need to put these numbers randomly in
columns B to M without any numbers repeating across the rows. Is this
possible?

 

[Mike H]

Hi,

If I understand correctly you want to pick 12 of these 20 numbers and put
them in B1 to M1 with no repeats. If so try this small macro.

ALT+F11 to open vb editor. Right click 'ThisWorkbook' and insert module and
paste the code in. Change the sheet name to the correct one and run it.

Sub Marine()
Dim FillRange As Range
Set Sht = Sheets("Sheet2") ' Change to suit
Set FillRange = Sht.Range("B1:M1")
For Each c In FillRange
Do
c.Value = Sht.Range("A" & Int((20 * Rnd) + 1))
Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Dave Peterson]

You may want to visit J.E. McGimpsey's site and grab a copy of his =RandInt()
function.
http://www.mcgimpsey.com/excel/udfs/randint.html

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Bernd P]

Hello,

I suggest to use my UDF RandInt:
http://sulprobil.com/html/randint.html

Regards,
Bernd