generating random numbers without repeats

L

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?
 
M

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.
 
D

Dave Peterson

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top