PDA

View Full Version : 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: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
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?

mdmackillop
07-18-2009, 05:51 PM
Thanks,
I posted the wrong file. Now corrected.
Regards
Malcolm

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?

mdmackillop
07-19-2009, 05:23 AM
I needed to know what heading (AFBT) that the correct answer was placed in
Apologies, I missed that bit

Select Case rnd1
Case Is < 0.15
cel.Offset(, -7) = "A"
cel.Offset(, -4) = cel
cel.Offset(, -3) = arr(0)
cel.Offset(, -2) = arr(1)
cel.Offset(, -1) = arr(2)
Case Is < 0.35
cel.Offset(, -7) = "F"
cel.Offset(, -3) = cel
cel.Offset(, -4) = arr(0)
cel.Offset(, -2) = arr(1)
cel.Offset(, -1) = arr(2)
Case Is < 0.65
cel.Offset(, -7) = "B"
cel.Offset(, -2) = cel
cel.Offset(, -4) = arr(0)
cel.Offset(, -3) = arr(1)
cel.Offset(, -1) = arr(2)
Case Else
cel.Offset(, -7) = "T"
cel.Offset(, -1) = cel
cel.Offset(, -4) = arr(0)
cel.Offset(, -3) = arr(1)
cel.Offset(, -2) = arr(2)
End Select

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?

mdmackillop
07-19-2009, 08:12 AM
Happy to help.
Please be careful with your details


except for Column N where I needed to know what heading (AFBT) that the correct answer was placed in.

You can mark a thread solved using the Tread Tools dropdown.
Regards
MD

mdmackillop
07-26-2009, 03:07 AM
Hi Sam,
Have you seen this site (http://www.numeracysoftware.com/freeExcel.html) for teaching samples?
Regards
MD