Generate one random at each time

[YasserKhalil]

Hello everyone
Suppose I have five words in A1 to A5
How can I generate a random value of the five values ..? and if I run the code again to get another new random value and so on till the words is over and when the words are over then to display a message that indicates that?

[HansV]

Should each word be different, or are repetitions allowed?

[YasserKhalil]

Each word would be different .. no repetitions and even if there are repetition, I need to display the content for just once .

[HansV]

What form should the output have? Do you want to display the words in a cell, or in a message box, or on a userform, or ...?

[YasserKhalil]

Any form .. for example a message box and I will try to implement that. Thanks a lot for your interest.

[HansV]

Here is a macro:

Code: Select all

Sub ListRandom()
    Static Words
    Static n As Long
    Static m As Long
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim tmp
    On Error GoTo ErrHandler
    tmp = Words(1, 1)
Main:
    m = m + 1
    If m > n Then
        If MsgBox("All words have been used! Do you want to start over?", vbYesNo + vbQuestion) Then
            Erase Words
        End If
    Else
        MsgBox "Random word: " & Words(m, 1)
    End If
    Exit Sub
ErrHandler:
    Words = Range("A1:A5").Value
    n = UBound(Words)
    ' Shuffle 3 times
    For i = 1 To 3
        For j = 1 To n
            k = Application.RandBetween(1, n)
            tmp = Words(j, 1)
            Words(j, 1) = Words(k, 1)
            Words(k, 1) = tmp
        Next j
    Next i
    m = 0
    Resume Main
End Sub

See the attached sample workbook.

RandomWords.xlsm

[YasserKhalil]

Great my tutor. Thank you very much for this masterpiece.