Change the position of the questions in a random way

[Visor]

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

 

[Deepak]

Something like this!!

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

 

[Visor]

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, ..

 

[Deepak]

I think u want to shuffle question and their 4 options rest will be static.

 

[Marc L]

Hi !

As it's very not difficult to attach before & after samples ! …
​

 

[Visor]

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.

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

 

[Marc L]

Is there always only 10 questions ?

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

 

[Deepak]

In addition to what marc asked!

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

 

[Marc L]

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 ! » …

VBA translation of his logic :​

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]

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

 

[Marc L]

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 …
​

 

[Visor]

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

 

[Marc L]

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 …
​

 

[Visor]

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

 

[Marc L]

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 …
​

 

[Visor]

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

 

[Visor]

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:

 

[Marc L]

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 …
​

 

[Visor]

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

 

[Marc L]

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 …​

 

[Visor]

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

    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

 

[Visor]

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

 

[Marc L]

[…] so it will not be necessary to use column E to identify the correct answer with the "x "

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 …
​

 

[Visor]

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

 

[Marc L]

but I have not seen additional solutions according to what you say

Solution is yet in the code as the logic is the same !

The file was uploaded and it was also explained before and after.

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

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

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 ...
​