Random help in populating cells

Volly

New Member
Joined
Apr 24, 2013
Messages
41
I'm creating a macro enabled form to create multiple choice test. I have 2 tabs - "Sim" for the testing page and "library" for where all my data is stored. I have a random function to pull at random the row where the question is: "library -column a" is the question, "library - column b" is answer 1 and so forth. What I'm having trouble wrapping my mind around is how do I put the 4 answers choices into an array then shuffle them so that each time the question is shown on the Sim sheet the list of possible answers are in a different order?

This is the code I have so far:
Code:
Private Sub CommandButton1_Click() 'generates question and 4 possible answers to choose from.
NQ = Sheets("library").Range("A1").End(xlDown).Row
Sheets("Sim").Range("B5:L17").ClearContents
RowNum = Application.RoundUp(Rnd() * NQ - 1, 0)
Cells(1, NQ).Value = RowNum
Sheets("Sim").Range("B" & Rows.Count).End(xlUp).Offset(4).Value = Sheets("library").Cells(RowNum, "A").Value 'question
'---------------------
'The next 4 values from the row in the library sheet need to be put into an array, shuffled, then inserted into the Sim sheet.
'The library sheet is never to be modified. The below just shows a non shuffled way of populating the fields. In the
'actual sim the below 4 lines are not use.
Sheets("Sim").Range("B" & Rows.Count).End(xlUp).Offset(2).Value = Sheets("library").Cells(RowNum, "B").Value 'answer from column b in the library sheet.
Sheets("Sim").Range("B" & Rows.Count).End(xlUp).Offset(2).Value = Sheets("library").Cells(RowNum, "C").Value 'answer from column c in the library sheet.
Sheets("Sim").Range("B" & Rows.Count).End(xlUp).Offset(2).Value = Sheets("library").Cells(RowNum, "D").Value 'answer from column d in the library sheet.
Sheets("Sim").Range("B" & Rows.Count).End(xlUp).Offset(2).Value = Sheets("library").Cells(RowNum, "E").Value 'answer from column e in the library sheet.
'-----------------------
'insert method to add the 4 possible answers that are shuffled here

'-----------------------

End Sub
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Forum statistics

Threads
1,202,993
Messages
6,052,968
Members
444,622
Latest member
Kriszilla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top