VBA - Generating numbers between 1-20 with no duplicates

[SwiftM]

Hi,

Basically, I have a button which when I click, inserts a number between 1 and 20 in cell "A1". When I click the button again, it realises that cell A1 is already taken and inserts another number between 1 and 20 in cell "A2"and so forth.

What I am struggling to do is finding a way so that the numbers are not repeated when I click the button? And when all the numbers (1 to 20) have been inserted up to "A20" then a message box appears stating that there are no numbers left.

This is my code so far: ( I am quite new to this so bare with me)

Dim MyMax As Long
MyMax = 20
Dim FillRange As Range
Set FillRange = Range("A1:A20")
With ThisWorkbook.Worksheets("October 2021")
Set FillRange = .Cells(.Rows.Count, 1).End(xlUp)
End With
If FillRange <> vbNullString Then
Set FillRange = FillRange.Offset(1, 0)
End If
For Each c In FillRange
Do
c.Value = Int((MyMax * Rnd) + 1)

Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next


Any help with this would be amazing as I have been trying all night and can't figure it out.

Thanks a lot

 

[SwiftM]

Welcome to the MrExcel board!

This is my interpretation of what you are asking.
Put a heading in cell A1, say "Numbers", then use this code for each button click.

Sub RndNums()
  Dim nums As Variant
  Dim i As Long, lr As Long
 
  Randomize
  nums = Split(Join(Application.Transpose(Evaluate("Row(1:20)"))))
  With Sheets("October 2021")
    lr = .Range("A" & Rows.Count).End(xlUp).Row
    If lr = 21 Then
      MsgBox "No numbers left"
    Else
      For i = 2 To lr
        nums(.Range("A" & i).Value - 1) = "x"
      Next i
      .Range("A" & lr + 1).Value = Filter(nums, "x", False)(Int(Rnd() * (21 - lr)))
    End If
  End With
End Sub

This is perfect!! Thank you Peter. Exactly what I wanted. Hoping to write codes like this one day

 

[SwiftM]

Thanks very much for all your replies guys! Really is appreciated! I am enjoying learning VBA but is much harder than I anticipated.

Marc

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

 

[kvsrinivasamurthy]

As did post 3 code

Another possible non-looping method, with easy adjustment if the upper limit of the number range is changed.

Sub RndNums_v2()
  Dim n As Long
 
  Const H As Long = 20 '<- Adjust to Highest possible number
 
  With Range("A1").Resize(H)
    n = Evaluate("count(" & .Address & ")")
    If n = H Then
      MsgBox "All " & H & " numbers used"
    Else
      .Cells(n + 1) = Evaluate("let(s,sequence(" & H & "),index(filter(s,isna(match(s," & .Address & ",0))),randbetween(1," & H - n & ")))")
    End If
  End With
End Sub

But coding is different.

 

[JEC]

The no loop method is not always a real VBA solution. More like converting Excel formulas in VBA. It is shorter but not necessarily better than a pure VBA solution, which works in every Excel version.

 

[Peter_SSs]

The no loop method is not always a real VBA solution. More like converting Excel formulas in VBA. It is shorter but not necessarily better than a pure VBA solution, which works in every Excel version.

I agree that shorter is not necessarily better. Non-looping is also not necessarily better than looping.
Not so sure about your idea of "real vba" though. I'm thinking that none of the suggestions in this thread would qualify as "real vba" for you?

I guess the measure of "better" or "best" in a forum like this is what suits the OP best in their opinion. Sometimes it might be
- the first answer they read that does what they want
- the fastest executing code
- the shortest code
- the code they understand the best
- the code they think will be easiest to maintain or modify in the future
- the helper who has given them good answers in the past
- etc

 

[JEC]

Not so sure about your idea of "real vba" though. I'm thinking that none of the suggestions in this thread would qualify as "real vba" for you?

@Peter, ofcourse it is VBA. But for example posts 2 and 5 are just Excel (365) formula's in VBA.

=SORTBY(SEQUENCE(20);RANDARRAY(20))

That is why I like the 3rd post the best, not mixing up Excel formulas with VBA code (Excel formula's in VBA are not easy to read, since they don't light up in the formula bar like they do on the worksheet).
Anyway, they are all nice solutions

 

[Peter_SSs]

That is why I like the 3rd post the best, not mixing up Excel formulas with VBA code

If you mean my post, then isn't this still using a worksheet formula in the vba?

Evaluate("Row(1:20)")

 

[JEC]

It is, but you don't have to read this as a complex (array)Formula in VBA.

 

[mikerickson]

This is a semi-looping solution, it only loops once.

Sub NextRnd()
    Static arrNumbers(1 To 20) As Long
    Static numberPointer As Long
    Dim i As Long, temp As Long
 
    If (numberPointer < 1) Then
        For i = 1 To UBound(arrNumbers): arrNumbers(i) = i: Next i
        numberPointer = 1
    End If
 
    i = Int(Rnd() * (UBound(arrNumbers) - numberPointer + 1)) + numberPointer
 
    temp = arrNumbers(numberPointer)
    arrNumbers(numberPointer) = arrNumbers(i)
    arrNumbers(i) = temp
 
    Range("A65536").End(xlUp).Offset(1, 0).Value = arrNumbers(numberPointer)
    numberPointer = (numberPointer Mod UBound(arrNumbers)) + 1
End Sub