VBA Help for Random Number Generator

[LAAdams17]

I would greatly appreciate help for a problem that I don't think is too difficult but my VBA are still ramping up. My objective is based on the picture of the table below:

• For the table below, using VBA produce 36 (F5) unique and random numbers between 1 and 70 (F3)
• Sorted from lowest to highest with the lowest number in the list showing in cell A11 (first row below 'Record Numbers to Test:'), 2nd lowest number in A12, etc
• Pressing the 'Generate' button (F7) will clear the old list of number and replace it with a new list base on two variable (F3 & F5)

-------------Text of Spreadsheet Below (If there's a way to post the actual spreadsheet, please let me know)----------------

Generate List of Record Numbers to Test
Step 1:				Enter the total number of records:	70
Step 2:				Enter number of records to test:	36
Step 3:				Press 'Generate' button:	Generate
Record Numbers to Test:

<tbody>
</tbody>

-------------------------------------------------------------------------------

THANK YOU in advance!
Lon

 

[footoo]

Sub Generate()
Dim nbr%, ray() As Integer
Dim lr%, i%, n%, coll As New Collection
If Not IsNumeric([F3]) Or Not IsNumeric([F5]) _
    Or WorksheetFunction.CountA([F3,F5]) <> 2 Then
    MsgBox "Only numbers must be entered in F3 and F5"
    Exit Sub
ElseIf [F3] < [F5] Then
    MsgBox "The number in F3 must not be less than the number in F5"
    Exit Sub
End If
nbr = [F5]
ReDim ray(1 To nbr)
lr = Cells(Rows.Count, "A").End(xlUp).Row
If lr > 10 Then Range("A11:A" & lr).ClearContents
For i = 1 To [F3]
    coll.Add i
Next
For i = 1 To nbr
    n = Int(Rnd * (coll.Count - 1)) + 1
    ray(i) = coll(n)
    coll.Remove n
Next
With [A11].Resize(nbr)
    .Value = Application.Transpose(ray)
    .Sort Key1:=[A11], Order1:=xlAscending, Header:=xlNo
End With
End Sub

 

[LAAdams17]

All I can say is WOW!!! and THANK YOU! It works perfectly. Thanks so much! I've been working on it but not sure I would have gotten there and it NEVER would have worked as well as what you provided.

 

[footoo]

Small revision :

Sub Generate()
Dim nbr%, ray() As Integer
Dim lr%, i%, n%, coll As New Collection
If Not IsNumeric([F3]) Or Not IsNumeric([F5]) _
    Or WorksheetFunction.CountA([F3,F5]) <> 2 [COLOR=#ff0000]Or [F5] < 1 Then[/COLOR]
[COLOR=#ff0000]    MsgBox "Only positive numbers must be entered in F3 and F5"[/COLOR]
    Exit Sub
ElseIf [F3] < [F5] Then
    MsgBox "The number in F3 must not be less than the number in F5"
    Exit Sub
End If
nbr = [F5]
ReDim ray(1 To nbr)
lr = Cells(Rows.Count, "A").End(xlUp).Row
If lr > 10 Then Range("A11:A" & lr).ClearContents
For i = 1 To [F3]
    coll.Add i
Next
For i = 1 To nbr
    n = Int(Rnd * (coll.Count - 1)) + 1
    ray(i) = coll(n)
    coll.Remove n
Next
With [A11].Resize(nbr)
    .Value = Application.Transpose(ray)
    .Sort Key1:=[A11], Order1:=xlAscending, Header:=xlNo
End With
End Sub

 

[LAAdams17]

You rock! Thanks again.

 

[Rick Rothstein]

Small revision :

Sub Generate()
Dim nbr%, ray() As Integer
Dim lr%, i%, n%, coll As New Collection
If Not IsNumeric([F3]) Or Not IsNumeric([F5]) _
    Or WorksheetFunction.CountA([F3,F5]) <> 2 [COLOR=#ff0000]Or [F5] < 1 Then[/COLOR]
[COLOR=#ff0000]    MsgBox "Only positive numbers must be entered in F3 and F5"[/COLOR]
    Exit Sub
ElseIf [F3] < [F5] Then
    MsgBox "The number in F3 must not be less than the number in F5"
    Exit Sub
End If
nbr = [F5]
ReDim ray(1 To nbr)
lr = Cells(Rows.Count, "A").End(xlUp).Row
If lr > 10 Then Range("A11:A" & lr).ClearContents
For i = 1 To [F3]
    coll.Add i
Next
For i = 1 To nbr
    n = Int(Rnd * (coll.Count - 1)) + 1
    ray(i) = coll(n)
    coll.Remove n
Next
With [A11].Resize(nbr)
    .Value = Application.Transpose(ray)
    .Sort Key1:=[A11], Order1:=xlAscending, Header:=xlNo
End With
End Sub

@footoo,

You need to add a Randomize statement to your code, otherwise (if I remember correctly) the next time the workbook is opened (after having been closed), the same set of random numbers as the previous time the workbook was opened will be regenerated again.


@LAAdams17,

Here is another macro that you can consider using...

Sub Generate()
  Dim Cnt As Long, RndIdx As Long, Tmp As Long, Arr As Variant
  If [F3&F5] Like "*[!0-9]*" Or [OR(F3=0,F5=0)] Then
    MsgBox "Only positive numbers must be entered in F3 and F5"
  ElseIf [F3< F5] Then
    MsgBox "The number in F3 must not be less than the number in F5"
  Else
    Randomize
    Arr = Evaluate("ROW(1:" & [F3] & ")")
    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
    Range("A11:A" & Rows.Count).ClearContents
    Range("A11").Resize([F5]) = Arr
    Range("A11").Resize([F5]).Sort [A11], xlAscending
  End If
End Sub

 

[footoo]

You need to add a Randomize statement to your code, otherwise (if I remember correctly) the next time the workbook is opened (after having been closed), the same set of random numbers as the previous time the workbook was opened will be regenerated again.

Thanks. Forgot to include it.

 

[LAAdams17]

Rick, very helpful. I really appreciate your input! Best regards, Lon