How to check if there's a duplicate in a column

[raven1124]

Hello Guys,

I need your help with injecting a duplicate checker within the code that I created.
Basically, this code generates random number from 0-5 and gives random emotion based from the number that was generated. Unfortunately, I want to make sure that these values will not be the same for each row to make it more "unique".

I tried to use "If" function but it only validates one cell at a time. Any help will be greatly appreciated.

Dim wsRandomizer As Worksheet
Private Sub Randomizer_Click()

Dim word As String
Dim checker As Integer
Dim xlRange As range

Set wsRandomizer = ThisWorkbook.Worksheets("Randomizer")
Set xlRange = wsRandomizer.range("A2:A7")

Dim x As Integer
Dim y As Integer

Application.ScreenUpdating = False
x = 2



'wsRandomizer.range("ToClear").ClearContents

Do While wsRandomizer.Cells(x, 1).Value = "" And x <> 8
    
    wsRandomizer.Cells(x, 1) = "=MOD(RAND()*100,5)"
    wsRandomizer.Cells(x, 1) = Round(wsRandomizer.Cells(x, 1).Value, 0)
    
   
    y = wsRandomizer.Cells(x, 1)
    Select Case y
    
        Case 5
        word = "Happy"
        wsRandomizer.Cells(x, 2) = word
        
        Case 4
        word = "Sad"
        wsRandomizer.Cells(x, 2) = word
        
        Case 3
        word = "Sleepy"
        wsRandomizer.Cells(x, 2) = word
        
        Case 2
        word = "Angry"
        wsRandomizer.Cells(x, 2) = word
        
        Case 1
        word = "Confused"
        wsRandomizer.Cells(x, 2) = word
        
        Case 0
        word = "Neutral"
        wsRandomizer.Cells(x, 2) = word
        
    End Select
    

x = x + 1
Loop

Application.ScreenUpdating = True
End Sub

 

[Jaafar Tribak]

One way that worked for me :

Private Sub Randomizer_Click()
    Dim oCol As New Collection
    Dim iRndVal As Integer
    Dim iCounter As Integer
    Dim sChoice As String
    
    On Error Resume Next
    Do
        Err.Clear
        Randomize
        iRndVal = Evaluate("=INT(MOD(RAND()*100,6))") + 1
        sChoice = Choose(iRndVal, "0 Neutral", "1 Confused", "2 Angry", "3 Sleepy", "4 Sad", "5 Happy")
        oCol.Add sChoice, CStr(sChoice)
        If Err = 0 Then
            Worksheets("Randomizer").Cells(iCounter + 2, 1) = Split(oCol.Item(iCounter + 1), " ")(0)
            Worksheets("Randomizer").Cells(iCounter + 2, 2) = Split(oCol.Item(iCounter + 1), " ")(1)
            iCounter = iCounter + 1
        End If
    Loop Until oCol.Count = 6
End Sub

 

[raven1124]

Hello Sir,

I'm a bit new with macro. Can you explain to me how your code works?

and how you use "Choose" to make an array of String instead of switch?

 

[Jaafar Tribak]

The code works like this :

It first randomly generates a number from 1 to 6. This random number is then passed to the VBA Choose function as an index and returns the string at that index from the string array.

Now that we have our first string retrieved from the array, we add it to a collection and we also pass the same string as the collection item key. If the key is already assigned to a previous item in the collection, the code throws an error so we go back and start the loop again. If no error (ie: the string doesn't exist in the collection), we add the string to the worksheet cell and start looping again for the next string.

The loop continious until all the strings in the array are retrieved and the collection count equals 6.

 

[raven1124]

Thank you for replying on this one. I tried multiple words using the choice function but it only display the first word.

Is there an alternative function if multiple string will used?

Thanks for explaining your function