Selecting Random Cells Using VBA

[omagoodness]

I am creating a game board where I want to generate a random response to a listBox selection. My list box displays a list of categories. The player selects a category and I want Excel to select a random word/name etc. from a correcsponding list. For example, one list item is "The Oscars". I have a list with the names of 10 Oscar winners and I want excel to randomly select from the list of 10 names. In the sheet, I can accomplish this with the formula

=INDEX(Lists!H17:H27,RANDBETWEEN(1,10))

.

I have a user form for player interaction where the player selects from a list of 10 categories. Each category has a correcpsonding table of data and I have created variables for each list. I want to use VBA to make the random selection, but I cannot figure out how to translate the above formula to vba code. Can anyone help?

 

[Micron]

Sub getRnd()
Dim varRnd As Variant

Randomize
varRnd = Int(9 * Rnd + 1)
Debug.Print varRnd

End Sub

10 values is a small sample size to work with, so you could find that the same value is picked if you run that in quick succession. You may need a module level variable of some type, to run the sub again if the last number was the same. If you want to ensure that the same number in a category is not repeated, that variable probably has to be an array. The the variable would need to be reset if you change the category. Anyway, I hope I answered the question, but as usual, it comes with what-if's.

 

[kevin9999]

In answer to your specific question (how to express your formula in VBA code), try:

Dim s As String
s = WorksheetFunction.Index(Worksheets("Lists").Range("H17:H27"), WorksheetFunction.RandBetween(1, 10))

then use the variable s as you need to.

 

[omagoodness]

In answer to your specific question (how to express your formula in VBA code), try:

Dim s As String
s = WorksheetFunction.Index(Worksheets("Lists").Range("H17:H27"), WorksheetFunction.RandBetween(1, 10))

then use the variable s as you need to.

Perfect. Thank you.

 

[omagoodness]

Sub getRnd()
Dim varRnd As Variant

Randomize
varRnd = Int(9 * Rnd + 1)
Debug.Print varRnd

End Sub

10 values is a small sample size to work with, so you could find that the same value is picked if you run that in quick succession. You may need a module level variable of some type, to run the sub again if the last number was the same. If you want to ensure that the same number in a category is not repeated, that variable probably has to be an array. The the variable would need to be reset if you change the category. Anyway, I hope I answered the question, but as usual, it comes with what-if's.

Thank you for your reply. I opted to use the method provided by kevin999 as I understand it better and I learn and remember when I have a clear understanding of what I am doing rather than just copying from someone else.

 

[kevin9999]

Glad we could help and thanks for the feedback