VBA or Formula Randomization Question

[Sephiroth]

Hello all so I have a couple questions that relate to the same topic. First I'm trying to create a "test" which pulls information, randomly from a different sheet, populates in the "primary" sheet and then upon completion of the test you can type in the box, anything you want and the scoring process will take place.

The problem is the excel formula I have only draws from the 124 (or whichever number of questions I specify) smallest, or largest (if i use SMALL() or LARGE() as my syntax) and doesn't give me a true randomized accounting from the list of questions I can choose from. The best I could come up with in formulas was by making a sheet that has SMALL() and one that has LARGE() as the primary syntax. Now that being said I have seen loads of VBA solutions. That also being said the solutions for VBA do not reference other sheets in order to generate the random questions (randomly selecting from a list of questions from a different sheet and populating them in the "primary" sheet).

I am not nearly as familiar with the VBA syntax or formula structure but I haven't been able to find an elegant solution through formulas either even using SUMPRODUCT(--(B2>=$B1:B1),--(B2<=$B1:B1)) to search from my question above all other questions and then pulling a question that doesn't currently exist.

This is my goal either through a macro solution or formula:

Look for a question in Sheet "Questions" at random, place it in sheet "randomized", repeat this process for N questions that could be user defined either by input or a cell, ensuring that no question repeats itself, and is being chosen from the full list of questions.

After that I should be able to answer the questions and either through prompt or upon answering the last question, grade the test and save it to the desktop as a PDF. (that would be a macro regardless).

If you do have a solution I would be very grateful for explanations as to how it works and why!

 

[Peter_SSs]

My example only has 10 questions, but I think all you need is this.

1. Set up your 'Questions' sheet like this. The formula in B2 is copied down. You can then hide column B if you want. (And if you have answers in column B, just move to the next available column)

Excel Workbook
	A	B
1	Questions	Random
2	Question 1	0.787378674
3	Question 2	0.91916663
4	Question 3	0.411397909
5	Question 4	0.289775704
6	Question 5	0.989261866
7	Question 6	0.192614181
8	Question 7	0.68934745
9	Question 8	0.689106534
10	Question 9	0.918216958
11	Question 10	0.677509986
Questions

2. Run the code below each time you want a new set of questions.
(If you changed what column the RAND() formula are in, then also adjust the red 'B' below to match that column.)

Sub RegenerateList()
  With Sheets("Questions")
    .Calculate
    .Range("A1").CurrentRegion.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom
  End With
End Sub

3. Then, if you want a set of, say, 6 questions, just keep drawing from A2:A7 as the questions will jumble each time the code is run.

 

[Sephiroth]

That was actually precisely what I needed to do thank you so much! My project is complete thanks to this!

 

[Peter_SSs]

That was actually precisely what I needed to do thank you so much! My project is complete thanks to this!

You are welcome. Glad it helped.