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?
Generate one random at each time
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Generate one random at each time
Should each word be different, or are repetitions allowed?
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Generate one random at each time
Each word would be different .. no repetitions and even if there are repetition, I need to display the content for just once .
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Generate one random at each time
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 ...?
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Generate one random at each time
Any form .. for example a message box and I will try to implement that. Thanks a lot for your interest.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Generate one random at each time
Here is a macro:
See the attached sample workbook.
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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Generate one random at each time
Great my tutor. Thank you very much for this masterpiece.