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