random unique number generator

[palaeontology]

I'd like to make a list of 16 numbers randomly generated from anywhere between 1 and the largest number found in column A.

I'm currently doing that in the range B3:B18 ... currently using the formula ... =RANDBETWEEN(1,MAX($A:$A))

However, I'd like the randomly generated list to have unique numbers, so no number in that list of 16 is repeated.

Is there a way to do that using formula ... I understand formula better than vba.

If not, then how could it be done using vba ?

Very kind regards,

Chris

 

[Takae]

Hope this helps.

Sub test()
Dim n As Long, i As Long
Range("B3:B18").ClearContents
For i = 3 To 18
    Do
        n = WorksheetFunction.RandBetween(1, 16)
    Loop While WorksheetFunction.CountIf(Range("B3:B18"), n) > 0
    cells(i, 2).Value = n
Next
End Sub

 

[palaeontology]

Thankyou, so much, for that, but I was hoping to have the random numbers range between 1 and whatever the largest value is in column A.

How could the above vba be amended to allow for that ?

Also, is it possible to have 30 of these lists generated ... each independent of the others .... in B3:B18, C3:C18, D3:D18, E3:E18, F3:F18, etc etc etc etc

Kind regards,

Chris

 

[Takae]

Please try again!
It doesn't have duplication on each column.

Sub test()
Dim n As Long, i As Long, j As Long
Range("B3:AD18").ClearContents
For j = 2 To 31
    For i = 3 To 18
        Do
            n = WorksheetFunction.RandBetween(1, WorksheetFunction.Max(Range("A:A")))
        Loop While WorksheetFunction.CountIf(Range(cells(3, j), cells(18, j)), n) > 0
        cells(i, j).Value = n
    Next
Next
End Sub

 

[alansidman]

You might want to adapt this code:

Option Explicit


Sub RandomNumbers()
    Dim x As Long
    Dim rn As Long
    Dim cn As String
    Dim sn As Long
    Dim en As Long
    cn = InputBox("Which Column to fill?")
    rn = InputBox("How many rows to fill?")
    'sn = InputBox("Start Number?")
    en = InputBox("Ending Number?")


    Application.ScreenUpdating = False
    x = 1
    Range(cn & "1").Select
    Do Until ActiveCell.Row = rn
        If ActiveCell.EntireRow.Hidden = False Then
            Debug.Print
            ActiveCell.Formula = WorksheetFunction.RandBetween(1, en)
            x = x + 1
        End If
        ActiveCell.Offset(1).Select
    Loop
    'Range(cn & "1:" & cn & rn).NumberFormat = "mm/dd/yy;@"
    'a = 1: b = 6: c = Int(Rnd() * (b - a + 1) + a)
    Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

Thankyou, so much, for that, but I was hoping to have the random numbers range between 1 and whatever the largest value is in column A.

How could the above vba be amended to allow for that ?

Also, is it possible to have 30 of these lists generated ... each independent of the others .... in B3:B18, C3:C18, D3:D18, E3:E18, F3:F18, etc etc etc etc

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub Generate()
  Dim Col As Long, Cnt As Long, RndIdx As Long, Tmp As Long, Arr As Variant
  Randomize
  Arr = Evaluate("ROW(1:" & [MAX(A:A)] & ")")
  For Col = 2 To 31  '30 columns from B to AE
    For Cnt = UBound(Arr) To LBound(Arr) Step -1
      RndIdx = Int((Cnt - LBound(Arr, 1) + 1) * Rnd + LBound(Arr, 1))
      Tmp = Arr(RndIdx, 1)
      Arr(RndIdx, 1) = Arr(Cnt, 1)
      Arr(Cnt, 1) = Tmp
    Next
    Cells(3, Col).Resize(16) = Arr
  Next
End Sub[/td]
[/tr]
[/table]