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

 

[Peter_SSs]

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

That does not seem to address the following. It just continues to produce numbers down column A, past A20

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.

 

[mikerickson]

A line testing numberPointer >= UBound(arrNumbers) can be inserted.