Modify Rand# Generator VBA

[VinceF]

Greetings,
I have a Form control button that runs this code, it generates a random# from 1 to 18 in cells A1, A2 & A3. How can I modify the code so that it doesn't duplicate a number?

Thank You,
VinceF
Excel 2016

Sub GenerateMultipleRandomIntegers()

Dim i As Integer

For i = 1 To 3
Range("A" & i) = WorksheetFunction.RandBetween(1, 18)
Next i

End Sub

 

[Joe4]

Here is one way:

Sub GenerateMultipleRandomIntegers()

Dim i As Integer
Dim n As Integer

For i = 1 To 3
    Do
'       Calculate number
        n = WorksheetFunction.RandBetween(1, 18)
'       Check to see if number already used in range
        If Application.WorksheetFunction.CountIf(Range("A1:A3"), n) = 0 Then
'           If not duplicate, assign to cell and exit loop
            Range("A" & i) = n
            Exit Do
        End If
    Loop
Next i

End Sub

 

[VinceF]

Here is one way:

Sub GenerateMultipleRandomIntegers()

Dim i As Integer
Dim n As Integer

For i = 1 To 3
    Do
'       Calculate number
        n = WorksheetFunction.RandBetween(1, 18)
'       Check to see if number already used in range
        If Application.WorksheetFunction.CountIf(Range("A1:A3"), n) = 0 Then
'           If not duplicate, assign to cell and exit loop
            Range("A" & i) = n
            Exit Do
        End If
    Loop
Next i

End Sub

 

[VinceF]

Thank you very much..!!!!! it appears to be working perfectly...

VinceF

 

[Joe4]

You are welcome.
Glad I was able to help!