Maybe a UDF like -
Function RandNumbers()
Dim R As Long, MatchId As Integer
Here:
MatchId = 0
R = Application.WorksheetFunction.RandBetween(1, 99)
On Error Resume Next
MatchId = Application.WorksheetFunction.Match(R, Range("A1:A20"), 0)
If MatchId > 0 Then GoTo Here:
RandNumbers = R
End Function
A slightly more customizable version, where you can select the Min, Max Number & Range. Should be entered like this - =RandNumbers(1,99,A1:A20)
Function RandNumbers(Min As Integer, Max As Integer, Rng As Range) As Integer
Dim R As Long, MatchId As Integer
Here:
MatchId = 0
R = Application.WorksheetFunction.RandBetween(Min, Max)
On Error Resume Next
MatchId = Application.WorksheetFunction.Match(R, Rng, 0)
If MatchId > 0 Then GoTo Here:
RandNumbers = R
End Function
Bookmarks