Solved: Teacher needs help in randomizing an answer into one of 4 multiple choice columns

**Sam62** · 07-18-2009, 02:41 PM

I need a routine that will go down a list of problems and place an answer into a randomized column.

columns
Cols = ___M_______N______O__P___Q__R______S_____W___X___Y
Heads = Ques __Corr Lett__ A___F__B___T___act ans_res1_Res2_Res3
row= __2+2=?_____ B______8___2__4___6_______4____2___8____6

I have 100+ questions like 2+2=? in column M with the actual answer in Column S and three incorrect answers in column W,X,Y. Can someone help me with a routine that will go down the question list and do these things:
1 Take the answer in col S and 10% of the time place it in column O under the heading A, 20% into col P, 30% into col Q and 40% into col R.
2. then look at where the correct answer was placed and get the heading (A, F, B, T) and place it in column N.
3. then take the incorrect responses in columns W,X Y and fill in the blank columns of O,P,Q or R.
I just don't know enough about VBA to figure out this routine.
Thanks

**Aussiebear** · 07-18-2009, 04:10 PM

Hi Sam62, In cases like this, it is far better to post a workbook with a befoe and after worksheets. That way we can play with the data to make sure it its your request.

**Sam62** · 07-18-2009, 04:20 PM

Hope this mkes it easier for someone to see what will help my classroom, thanks

**p45cal** · 07-18-2009, 05:07 PM

see attachment which contains this macro:[vba]Sub blah()
Range("o2:r101").Clear
For rw = 2 To 101
myrnd = [RANDBETWEEN(1,10)]
Select Case myrnd
Case 1
Cells(rw, "O").Value = Cells(rw, "S").Value
Cells(rw, "N").Value = "A"
Case 2, 3
Cells(rw, "P").Value = Cells(rw, "S").Value
Cells(rw, "N").Value = "F"
Case 4 To 6
Cells(rw, "Q").Value = Cells(rw, "S").Value
Cells(rw, "N").Value = "B"
Case Else
Cells(rw, "R").Value = Cells(rw, "S").Value
Cells(rw, "N").Value = "T"
End Select
i = 0
For Each cll In Range(Cells(rw, "O"), Cells(rw, "R")).Cells
If IsEmpty(cll) Then
cll.Value = Cells(rw, "W").Offset(, i).Value
i = i + 1
End If
Next cll
Next rw
End Sub
[/vba]edited after you posted your workbook:So the columns are different as well as the percentages; to accomodate the percentages change, change
[RANDBETWEEN(1,10)]
to
[RANDBETWEEN(1,100)]
and
Case 1
to
Case 1 to 15

Case 2, 3
to
Case 16 to 35

Case 4 To 6
to
Case 36 to 65

You can change the column refs.

**mdmackillop** · 07-18-2009, 05:29 PM

or
EDIT See below

**p45cal** · 07-18-2009, 05:39 PM

mdmackillop, I don't see any difference between the file you posted and Sam62's - have I missed something?

**Sam62** · 07-18-2009, 10:34 PM

To P45cal and Mdmackillop,
thanks both of you so much for your time and code. Here's what I found.
P45cal - your file that I downloaded had all the values in the right place so I know it worked but no matter if I change the 10 to 100 and the case statements, when I run either version it debugs on the first Case line. But because I can see the results, I must be doing something wrong.
Mdmackillop - thanks for sending back my file with the button on it - I place 200+ questions in and it built the columns the way I needed except for Column N where I needed to know what heading (AFBT) that the correct answer was placed in. Everything else worked.

Thanks to both of you - could you tell me what you think I'm doing wrong?

I found one thing -
I notice that in the spreadsheet on U18, I mentioned that after placing the correct answer into one of the Columns AFBT then whatever column that correct answer went to (AFBT), then place that column's letter in column G - that was wrong, it should go into Column N under "Corr Let" - Maybe that is one reason why I didn't see the letters in one of the spreadsheets.

**p45cal** · 07-19-2009, 01:11 AM

re "I must be doing something wrong"

While I can't look over your shoulder to see what's going on, might it be an idea to post your code?

**Sam62** · 07-19-2009, 08:09 AM

Thank you very much. I downloaded your code, mdmackillop, and changed 4 offsets from -7 to -5 and it works just the way I like. Thanks again. Thanks to P45cal too.

Do I mark somewhere in this forum, "Solved" or not?

Thread: Solved: Teacher needs help in randomizing an answer into one of 4 multiple choice columns

Thread Tools

Display

Solved: Teacher needs help in randomizing an answer into one of 4 multiple choice columns

Thanks for the suggestion - here is the workbook

Posting Permissions