Thanks for posting. +++++ *I did not do this for you. God is here working through me for you.*
On Wed, Nov 5, 2014 at 9:40 AM, Rajnish Malhotra < freeofcosthotels...@gmail.com> wrote: > 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. > -- 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.
