In my 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. 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!

I got an answer at excelforum and thought I should update the link http://www.excelforum.com/excel-prog...d-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 9:04:34 AM UTC+5:30, Rajnish Malhotra wrote:
In my 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. 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!