I got an answer at excelforum and thought I should update the link
http://www.excelforum.com/excel-programming-vba-macros/1047574-shuffle-four-answers-of-one-question-with-a-condition-and-loop-it.html
Thank you
Sub test()
Dim r As Long
Dim CA As String
For r = 1 To Cells(Rows.Count, "A").End(xlUp).Row Step 5
CA = Cells(r, "A").Offset(Asc(LCase(Left(Cells(r, "A").Value, 1))) -
96, 0).Value
Cells(r, "A").Offset(1, 1).Resize(4, 1).Formula = "=RAND()"
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=Cells(r, "A").Offset(1, 1).Resize(4, 1), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Cells(r, "A").Offset(1, 0).Resize(4, 2)
.Header = xlNo
.Orientation = xlTopToBottom
.Apply
End With
Cells(r, "A").Offset(1, 1).Resize(4, 1).Clear
CA = Chr(Application.Match(CA, Cells(r, "A").Offset(1, 0).Resize(4, 1),
False) + 96)
Cells(r, "A").Value = CA & Right(Cells(r, "A").Value, Len(Cells(r,
"A").Value) - 1)
Next r
End Sub
On Tuesday, November 4, 2014 6:58:41 AM UTC+5:30, Rajnish Malhotra wrote:
>
> In the attached excel file (version 2007), the first sheet is "quiz" and
> second sheet is "sheet1". It has a question/answer sheet (sheet1) that is
> designed in such a way that the first letter of the question is prefixed
> with the answer. For example here is a sample data in the range A1:A20
> which is also attached. Answer to first question is "b" hence the question
> is prefixed with "b".
>
> bWho is a cricketer?
> Tiger Woods
> Sachin Tendulkar
> Will Smith
> Bill Gates
> aWho is a Golfer
> Tiger Woods
> Sachin Tendulkar
> Will Smith
> Bill Gates
> dWho is an Entrepreneur
> Tiger Woods
> Sachin Tendulkar
> Will Smith
> Bill Gates
> cWho is an actor
> Tiger Woods
> Sachin Tendulkar
> Will Smith
> Bill Gates
>
> I need to shuffle/jumble the four options for every question (in sheet1)
> whenever the workbook is opened without changing the logic of prefixing
> answer to the question's first letter and keeping all other things intact.
> Please help me how can I achieve this. What kind of code can be written and
> which section of vba (sheet/module)? Here is an example of question # 1 how
> it should be after randomization (on file open).
>
>
> cWho is a cricketer?
> Will Smith
> Bill Gates
> Sachin Tendulkar
> Tiger Woods
>
>
> Here, the options are shuffled/jumbled and also the correct answer is
> updated in the question (prefixed by 'c' which was earlier 'b'). I was
> looking at the chip article at
> http://www.cpearson.com/excel/ShuffleArray.aspx but I am not able to
> implement this in my question. I also tried finding source on how we can
> make use of these functions that suits my question to no avail. I am still
> trying... even if i randomize the four options, how can i get the correct
> answer sequence (a, b, c, d) to prefix in the question is another tough
> task. Appreciate some help here. Thank you!
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
