Excel Game - VBA Random Question Help

[KDavidP1987]

Hey All,

This is the the other (bigger) issue I am having within an Excel training game I am creating.

Context:
I am in the process of developing a learning game for a training class at work. It's based on the concept of the Around the World game we have likely all played with a Basketball. Except, in this game players cells move across the spreadsheet, From Column A (START) -> Column O (FINISH), which each column in between representing a chapter from the book.

(Wish there was a way I could include a picture of it, for visual reference, sorry!).

In the game, players/teams move forward or backward depending on their ability to answer questions from the content.

Question:
I have a table of questions spanning all chapters (1 - 13) of the book. I am trying to generate a VBA script which will get a random question from the table of questions, based on the players current position on the board (Column #).

I have figured out how to identify the players position (chapter), by using the Range("Blue").Column - 1 code. However, I can not for the life of me figure out how to get a random row of data out of the table with all the questions listed based on it.

FYI: Each row within the questions table includes the following columns: ChapterNumber, ChapterTitle, PgNumber, Type, Question, Answer.

So if the VBA could (on click) identify the position of the player (already Completed), then grab a random row from that chapter, and paste the data from each column on that row into the game that would be perfect.

I figure it will need to narrow down an array of rows matching the chapter the player is on, then from that array get 1 random row's data, put it into variables, and put each of those variables into the correlating cells on the game sheet.

Does anybody know a way to accomplish this?

Sincerely,

 

[Rijnsent]

Hi KDavidP,

for an image: include a link to a dropbox/onedrive/etc file. Given your example, if your player is e.g. in column C, you'd want the questions from chapter 3, right?
The pseudo code would be:
-get info from the questions Range
-loop through the column with ChapterNumber, counting the amount of times that you have a question for chapter 3
-get a random number (RANDBETWEEN) from 1 to the nr of times "chapter 3" is in your list. Say that number is 2, you'd want to pick the second question.

Please post your VBA code so someone can help you expanding your code,

Cheers,

Koen