VBA Code to generate unique random numbers based on criteria

[ddub25]

Can anyone help with adapting this code to generate random numbers in Column "L" from 1 to the total of the number of rows that meet the criteria? For example, there are 2 rows that meet the specified criteria and so random numbers 1 and 2 would be randomly generated in these rows each time I click the command button. Note that the random numbers should be unique and so the code should always generate a 1 and a 2 randomly. Thanks in advance.

Private Sub CommandButton2_Click()

Dim i As Long

For i = 5 To 28
If Range("G" & i).Value = "STR" And Range("H" & i).Value = 1 And Range("I" & i).Value = "H" And Range("K" & i).Value = "U2" Then
Range("L" & i).Value = Int((100 * Rnd) + 1)
End If
Next i

End Sub

 

[rlv01]

One way:

Private Sub CommandButton2_Click()
    Dim i As Long
    Dim n As Long
    Dim rFound As Range

    For i = 5 To 28
        If Range("G" & i).Value = "STR" And Range("H" & i).Value = 1 And Range("I" & i).Value = "H" And Range("K" & i).Value = "U2" Then
            Do
                n = Application.RandBetween(1, 999)
                Set rFound = Range("L:L").Find(What:=n, LookAt:=xlWhole)
            Loop Until rFound Is Nothing
            Range("L" & i).Value = n
        End If
    Next i
End Sub

 

[ddub25]

Thanks very much rlv01, problem solved. It's much appreciated.