1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Change the position of the questions in a random way

Discussion in 'VBA Macros' started by Visor, Dec 22, 2017.

  1. Visor

    Visor Member

    Messages:
    190
    Dear friends of the forum, I can not find a way to make the questions change randomly.
    What I have so far is that the position of the respospuestas options change randomly (Blue button). But I also want to make sure that you can change the whole question along with your answer options (Yellow button)
    When working the macro for the yellow button what is in column B, this should not change, because I do not want to change the number that indicates the question I just want to change the column C along with A and D.
    In column A: this is the type of question
    In column C: There are the questions along with your response options
    In column D: are the correct answers highlighted in yellow
    These when changing positions must be in the same rows where they change.
    I suggest first clicking on the blue button to review what I have until today.
    Thanks in advance
    I upload a file to be reviewed

    Attached Files:

  2. Deepak

    Deepak Excel Ninja

    Messages:
    2,799
    Something like this!!

    Code (vb):
    Option Explicit

    Sub change_Q()
    Dim q As Integer, vList As Variant, vQues As Variant, r As Integer

    vQues = Range("V1:V10")

    ReDim vList(0)
    For r = 11 To 74 Step 7
    re:
        q = Application.WorksheetFunction.RandBetween(1, 10)
       
        If Not IsEmpty(vList) Then
            If Not IsError(Application.Match(q, vList, 0)) Then GoTo re
        End If
        ReDim Preserve vList(UBound(vList) + 1)
        vList(UBound(vList)) = q
       
        Cells(r, 3).Value = Application.Index(vQues, q)

    Next

    Erase vList
    Erase vQues

    End Sub
     

    Attached Files:

    Thomas Kuriakose likes this.
  3. Visor

    Visor Member

    Messages:
    190
    Thanks Deepak, it's how I want you to function, .. now I'm going to try to see if I can make the whole question move, along with the response options ..
    For example, question 1, should move from rank A10 to A15; C10 up to C15 and D10 up to D15. that is to say everything that is with blue color frame will be observed that moves (image)
    Which I already see complicated, make the jumps.
    I hope to be able and comment, ..
    upload_2017-12-23_20-7-32.png
  4. Deepak

    Deepak Excel Ninja

    Messages:
    2,799
    I think u want to shuffle question and their 4 options rest will be static.
  5. Marc L

    Marc L Excel Ninja

    Messages:
    3,340

    Hi !

    As it's very not difficult to attach before & after samples ! …
  6. Visor

    Visor Member

    Messages:
    190
    Dear Deepak
    and
    Dear Marc L
    I do not see how to make it work as I want
    I've barely managed to make it work by jumping every 7 cells down instead of the code you gave me that runs one by one in a row as you did in column V1 through V10.

    Code (vb):
    Sub Rnd_Questions()
    Dim c As Variant
    Dim n As Integer
    For n = 10 To 78 Step 7
    c = Range("X" & n)
    MsgBox c         'To see Reaction!!
    Next n
    End Sub
    Now I have placed it preferably in Column X so that it is further away.
    In any case, what I do not achieve is that it is arranged in jumps, ... that the random one is generated and it is reordered in the AC and D column. Maybe it is convenient that everything moves from A to D and only in the column B looks like he does not move at all, that is, he always goes to see 1 abcd; 2 abcd; ... ..10 abcd as long as the macro works.

    I am sorry

    Ok, .. now I'm going to put a new file that shows how I initially uploaded it, and in the other page as it would be when I clicked, every time I have to click on the yellow button , it will have to change position randomly. You will notice that in addition to sheet 1 (before) I have placed in Column X, again the text of the questions. I put it that way because I realize that it is necessary to pass it to another side, for example, to column X and then return it to the original position that is column A but changed its position, as seen on the other sheet "result" (after)
    I hope my explanation has been improved. Likewise, if more explanation is required, please let me know.
    Thanks you

    Attached Files:

  7. Marc L

    Marc L Excel Ninja

    Messages:
    3,340

    Is there always only 10 questions ?

    'Cause it's at very beginner level to shuffle always same 10 questions …​
  8. Deepak

    Deepak Excel Ninja

    Messages:
    2,799
    In addition to what marc asked!

    Why not to setup a table like as below so that you can easily manage it.


    upload_2017-12-25_18-30-2.png
    Visor likes this.
  9. Marc L

    Marc L Excel Ninja

    Messages:
    3,340

    I asked a 13 years old child to think about this shuffle :
    « Easy, why he doesn't use his brain, just with a paper ? »
    My answer was « Maybe he does not know how to use it …
    So write the logic on a paper if you dare ! »
    Less than ten minutes after he gave me his paper
    with an « Easy like I told you ! » …

    [​IMG] VBA translation of his logic :​
    Code (vb):
    Private Sub AleatoriPosicionDePreguntas()
        Dim C&, P&, R&, V(), N&, A&, W
    With Hoja2
        With .Cells(.Rows.Count, 1).End(xlUp).CurrentRegion
            C = .Columns.Count
            P = .Row
            R = .Rows.Count
        End With
            ReDim V(1 To .Cells(P, 2).Value)
        For N = UBound(V) To 1 Step -1
            V(N) = .Cells(P, 1).Resize(R, C).Value
            P = P - R - 1
        Next
            Randomize
        For N = UBound(V) To 2 Step -1
            A = Fix(Rnd * (N - 1)) + 1
            W = V(A):  V(A) = V(N):  V(N) = W
        Next
            Application.ScreenUpdating = False
        For N = 1 To UBound(V)
            V(N)(1, 2) = N
            P = P + R + 1
            .Cells(P, 1).Resize(R, C).Value = V(N)
            .Cells(P + 1, 3).Rows.AutoFit
        Next
    End With
            Application.ScreenUpdating = True
    End Sub
    Do you like it ? So thanks to click on bottom right Like !
    Visor and Thomas Kuriakose like this.
  10. Visor

    Visor Member

    Messages:
    190
    Thanks dear Marc L,
    About,..
    Is there always only 10 questions?
    For now I have 500 questions, of which 10 will be generated, for each 5 students, they should have printed on a sheet so both the questions and the options must be reordered. They are daily evaluations of each class topic.
    On the other hand, in the practice on prgogramacion of this case, the logic is based on how much code vba one can master to solve something, I am microbiology and I like to look for applications of the vba in teaching, but I do not know much about vba and every time I see new functions I find more information to learn.
    I have ideas and needs the rest, we solve with the support of great teachers who learn a lot.
    This Excellent, just as I wanted it to work.
    I am very grateful
  11. Marc L

    Marc L Excel Ninja

    Messages:
    3,340

    I forgot to mention (as a training in less than 20 codelines) :
    a good code needs only a single loop, the adult way,
    logic at level of any microbiologist or even any student knowing
    how to make an addition and a subtraction ! As more loops is a kid way …
  12. Visor

    Visor Member

    Messages:
    190
    Greetings, however I found an error in the operation, insurance is due to my lack of explanation.
    The error is that the questions are not moving together with the respective correct answer to change position, especially each line (col C) with the answer of the front (Col D), since the 4 lines of the options must be together with the correct answer highlighted this time in green. I need you to be this way so that later I can create the correct answer sheet. Column D at the end I will leave it white and hidden. For today I wish it could work moving along with the lines options and correct answers from column D (already established)
    I upload the file so you can see it
    Excuse me

    Attached Files:

  13. Marc L

    Marc L Excel Ninja

    Messages:
    3,340
    Lack of explanations of the logic for the yellow color in Result worksheet
    versus the green & yellow colors of Test worksheet !

    But with logic of sample provided you can use Range.Copy method
    according to your need as it's at very beginner level !
    Or at least join clean before and after worksheets,
    it's easier and better if color format does not change …
  14. Visor

    Visor Member

    Messages:
    190
    Thank you, the yellow fill leaves you to show that you change the position of the correct answer .. in column D. Now I uploaded the file with green highlighting options to try it and realize that it does not connect properly by changing the whole question.
    That is, the block that you should always change is the type of question in column A, the question's statement, the options in the question along with the correct answer that is in column D.
    I'm sorry, but sometimes the most basic thing has made us stagnate, and since I'm not really a programmer, it's even more complicated for me.
    I'll see how it goes with Range.copy
  15. Marc L

    Marc L Excel Ninja

    Messages:
    3,340

    Range.Copy method is only to keep background colors but if in real
    worksheet there is no such color, so it's very not the clever way !

    As it's very not difficult to attach before & after worksheets
    respecting the real layout …
  16. Visor

    Visor Member

    Messages:
    190
    Thanks Marc L for continuing with this topic.
    mmm I already understand but I need a way to identify the correct answer from Column D, and I saw that it is possible with fill color.:(

    Actually it is not that you want the fill colors of the answer options, you can use it in the previous post to show the error that occurs, ... it is only necessary to indicate the correct answers from Column D, there it is necessary to identify it and be able to make the corrections template.
    I am reviewing the Range.copy function and it seems good to use it but always that when I return to the same place I can change the position of the whole structure block of each complete question.
    Before after
    Question1 Thank you Marc L for continuing with this topic. Actually it is not that you want the fill colors of the answer options, you can use it in the previous post to show the error that occurs, ... it is only necessary to indicate the correct answers from Column D, there it is necessary to identify it and be able to make the corrections template.
    I am reviewing the Range.copy function and it seems good to use it but always that when I return to the same place I can change the position of the whole block of the structure of each complete question or Block

    Each block = Type of question TP (col A)
    Statement of the question and answer options (Col C), Correct answer (Col D) example: Block 1 or question1 A10: D15
    Block 2 or question2 A18: D22
    Before (Each block) After (random position)
    Question1 Question8
    Question2 Question5
    Question3 Question2
    Question4 Question7
    Question5 Question1
    Question6 Question4
    Question7 Question6
    Question8 Question3
    Question9 Question10
    Question10 Question9

    The code you gave me works changing position,.. good!!, except that the color fill (now green) of the correct answer does not follow or is not together with the correct answer option
    If you have another suggestion to identify the correct answer I would like to know. since that I need to automatically create the response template using macros. Subsequently, my proposal is that with macros, it is self-qualified using a code of answers that the student has made according to his answers.

    Unless in column E, I place a mark by placing an "x" in the correct answer, but the code you gave me must also contain column E to change position
    Last edited: Dec 28, 2017
  17. Visor

    Visor Member

    Messages:
    190
    To tell the truth, I do not know where to incorporate Column E into your code so that it is included in the change of position.
    I think it is the most convenient, if we know that it is the only way.

    The final project is long but it is about making a way of qualifying physical or paper evaluations more quickly and at the same time it is with random questions (one exam for each student). The strategy, .. using codes generated by the student, these must coincide with the codes generated by the macro to reach the maximum grade. For now I am stuck here as you have already seen it.

    How can I incorporate Column E into the code you gave me earlier?

    example:
    upload_2017-12-28_9-18-42.png
  18. Marc L

    Marc L Excel Ninja

    Messages:
    3,340
    Now there is a column E ‼
    The mod is at beginner level just respecting Logic !

    Before creating a thread, you must think about your need
    in order to post a crystal clear & complete explanation
    with a workbook sample respecting real layout !

    Ok last chance : I will post a one shot code
    (last code, I will not mod it if you forget again something)
    after you post before and after sample workbooks respecting real layout
    as you should have done since the initial post of this thread !
    So just well think about your need …
  19. Visor

    Visor Member

    Messages:
    190
    Yes!!, it works with column E, it moves everything together. Actually I did not know that the fill colors were not going to move with Macros, so the initial file that I uploaded was made with that design. Now that I learned that, I had to change to the "x" in column E
    I have tried it several times and everything is fine.
    I thank you very much for all your support
    A thousand apologies for the inconvenience
    I am very grateful Deepak too
  20. Marc L

    Marc L Excel Ninja

    Messages:
    3,340


    Have you succeeded to add the shuffle part for a,b,c,d lines
    like in post #12 sample ? If positive, I wanna see your code …​
  21. Visor

    Visor Member

    Messages:
    190
    Well, I had to make an artifice, and resign myself with it that the filling can not be moved

    The artifice is to follow the x and place or cancel the filling a cell before.
    However, I think that if I select the option with answer 3 this three does not change in the block, and there should be a way to follow it to 3 so it will not be necessary to use column E to identify the correct answer with the "x "
    I show you the code that I added, with the support of a great teacher, his name is Antoni


    Code (vb):
        For Each celda In Range("E12:E" & R + P)
            If celda = "x" Then
                celda.Offset(0, -1).Interior.Color = vbGreen
            Else
                If celda.Offset(0, -1).Interior.Color = vbGreen Then
                  celda.Offset(0, -1).Interior.ColorIndex = xlNone
                End If
            End If
        Next celda
  22. Visor

    Visor Member

    Messages:
    190
    Dear Deepak

    You propose me another model of random questionnaire to change questions and answer options, although doing it in formulas for rows can be a bit simple, but it would not work for the columns of the answer options. Even more as I prefer exclusively using the macros excel vba. I would like to know if you see the issue as well, and that you can show me an example with macros with your model of pos # 8.
    I upload a file for you to use

    Note: I will always select only 10 random questions with your respective answer options random also, per class subject

    Attached Files:

  23. Marc L

    Marc L Excel Ninja

    Messages:
    3,340
    It's quite easy but only when you join at least a before and an after
    samples workbooks, both respecting the same real layout !
    As here it's just about a child level logic …
  24. Visor

    Visor Member

    Messages:
    190
    Dear Marc L, I am very grateful for the code you gave me because it works perfect for me. After that, you just went by saying that everything is easy and that it is a child thing, but I have not seen additional solutions according to what you say now. The file was uploaded and it was also explained before and after. If until now it has not been possible to understand, I can not find a way to clarify the subject better.
    As I repeat, I am very grateful for all that you have helped me.
    I am not an expert in programming especially in macros vba, I am not computer and I also need to use the google translator to communicate with you because I suppose you do not know the Spanish language and then respecting your language, I am the one who must expose the subject in English language. Sometimes the logic is part of having knowledge but also of having experience, and you have all that.
    Thanks once again
  25. Marc L

    Marc L Excel Ninja

    Messages:
    3,340
    Solution is yet in the code as the logic is the same !

    Which one ?!
    As layout and colors changed, even within the same workbook ! ...

    English is not my native language and, even if I know a bit of Spanish,
    as here it's an english forum we must use english.

    And you can see for spanish Excel forums ...

Share This Page