create student questionnaire selected in combobox based on question pattern

Visor · Jan 18, 2018

Dear friends of the forum, I would like you to help me by completing a macro to reconstruct in the gray area (Test Sheet) the questionnaire, based on a pattern of distribution of questions and answer options, this pattern is on the Nomina sheet. As you will see when opening the userform we must select the student and then the win button must reconstruct the questionnaire according to the established pattern. What I have managed to do this reconstruction but as you can realize the options are not located properly (the option 1 -1 with the number 1, etc)
in each student as selected by the student.
I appreciate your kind collaboration.
I upload a file to be seen

Visor · Jan 18, 2018

Greetings, here I post an image to reinforce the explanation of the previous publication, where my problem is in each question.

It should be like this:

It should be the same number of in front
3-2
3-1
3-3
3-4

Marc L · Jan 19, 2018

Hi !

Read Split VBA inner help in order to separate elements (words) …

Visor · Jan 19, 2018

Greetings, I do not want to separate words from a cell

if I use this pattern:

It is also the text of the answer options that do not match, the rest, I have managed to do as you can see when executing the macro

Marc L · Jan 19, 2018

It was just an easy way for your post #2 …

Visor · Jan 19, 2018

Thanks, I repeat only the problem that is shown in the post # 2 in which I have the problem and I do not know how to solve it.
Please, if there is a solution to this, I would like to know what the code would be.
But if there is another alternative I would also like to know the code
I have already tried, but I can not make it work

Marc L · Jan 19, 2018

Just let a chance to Split function, read & try !
Or see easy VBA text functions like Left, Len, …
Or even use a worksheet formula.

Visor · Jan 19, 2018

mmm,.. I´m goingo to try split function

I think, I will try something like that

part options
if split(....right)... <> Hoja2.cells(AA12,...) Then
or maybe
if split(....right).... = Hoja2.cells(AA12,...) Then

I hope to achieve it

Marc L · Jan 19, 2018

With Split you need nothing else !
And Right is not in the other equation as I wrote Left …

Chihiro · Jan 22, 2018

Not exactly sure of the whole process. But I assumed that you just wanted numeric after "-" in C column to match D column number.

As Marc has stated. You'd use Split value that's being placed into C, and then concatenate "-" and value in D column.

So...

Code:

'para el texto de las opciones de respuesta
Hoja2.Cells(r + 1, 3) = Split(C.Offset(1, -3), "-")(0) & "-" & Hoja2.Cells(r + 1, 4)
Hoja2.Cells(r + 2, 3) = Split(C.Offset(2, -3), "-")(0) & "-" & Hoja2.Cells(r + 2, 4)
Hoja2.Cells(r + 3, 3) = Split(C.Offset(3, -3), "-")(0) & "-" & Hoja2.Cells(r + 3, 4)
Hoja2.Cells(r + 4, 3) = Split(C.Offset(4, -3), "-")(0) & "-" & Hoja2.Cells(r + 4, 4)

Visor · Jan 22, 2018

Hola, Chihiro,
Audicioné
No quiero mostrar los números al final de cada opción de respuesta, lo puse para poder guiar y verificar que todo se corresponda según el patrón.

[ATTACH = completo] 49134 [/ ATTACH]

Las opciones de respuesta se deben escribir de la siguiente manera:
[ATTACH = completo] 49135 [/ ATTACH]

Hice una prueba, por ejemplo, la pregunta que aparece en la plantilla es como esta:
Ver hoja X10 (2) (molde)
[ATTACH = completo] 49133 [/ ADJUNTO]
Hola , está en frente de los 3 en este molde

Después de ejecutar la macro, el resultado fue el siguiente:

Ver hoja A11 (2)
[ATTACH = completo] 49136 [/ ATTACH]

Hola, debo estar delante del 3 en el patrón porque en el molde está en 3
Del mismo modo para cada una de las otras opciones, 1, 2 y 4 del molde deben estar en el patrón

Total macro:
[CÓDIGO] Sub Patrón_Quiz ()

'Funciona
fila = 11
Para col = 3 a 12
Hoja2.Cells (fila, 1) = Hoja4.Cells (10, col)
'Hoja2.Cells (fila, 1) .Interior.Color = vbYellow
fila = fila + 7
Siguiente col

'Coloca el Número de preguntas de las 10 del patron aleatorio construido
fila = 11
Para col = 16 a 61 Paso 5
Hoja2.Cells (fila, 4) = Hoja4.Cells (10, col)
'Coloca las opciones
Hoja2.Cells (fila + 1, 4) = Hoja4.Cells (10, col + 1)
Hoja2.Cells (fila + 2, 4) = Hoja4.Cells (10, col + 2)
Hoja2.Cells (fila + 3, 4) = Hoja4.Cells (10, col + 3)
Hoja2.Cells (fila + 4, 4) = Hoja4.Cells (10, col + 4)
'Hoja2.Cells (fila, 1) .Interior.Color = vbYellow
'Hoja2.Cells (fila, 1) .Interior.Color = vbGreen
fila = fila + 7
Siguiente col
End Sub

Sub ReorganizarPop ()
'Ahora funciona
r = 11
Para i = 16 a 61 Paso 5
buscnum = Hoja4.Cells (10, i)
Set C = Hoja2.Columnas (29) .Find (What: = buscnum, LookIn: = xlValues, LookAt: = xlWhole, SearchDirection: = xlNext)
Si C no es nada entonces
MsgBox "'op' inexistente".
Salir Sub
Terminara si
'Para el texto del tipo de pregunta ... ??
Hoja2.Cells (r, 3) = C.Offset (0, -3) 'Para el texto de las preguntas
'para el texto de las opciones de respuesta
Hoja2.Cells (r + 1, 3) = Split (C.Offset (1, -3), "") (0) & "" y Hoja2.Cells (r + 1, 4)
Hoja2.Cells (r + 2, 3) = Split (C.Offset (2, -3), "") (0) & "" y Hoja2.Cells (r + 2, 4)
Hoja2.Cells (r + 3, 3) = Split (C.Offset (3, -3), "") (0) & "" y Hoja2.Cells (r + 3, 4)
Hoja2.Cells (r + 4, 3) = Split (C.Offset (4, -3), "") (0) & "" y Hoja2.Cells (r + 4, 4)

r = r + 7
Siguiente yo
End Sub [/ CODE]
result

Visor · Jan 23, 2018

I'm sorry I do not know what happened, with my automatic translator,
Here I repeat my answer:

Hi, Chihiro,
Thanks for your help
I do not want to show the numbers at the end of each answer option, I put it in order to guide and verify that everything corresponds according to the pattern.

The response options should be written as follows:

I did a test, for example, the question that appears in the template is like this:

See sheet(2) X10 (mold)

Hi word, it's in front of the 3 in this mold

After executing the macro, the result was the following:
See sheet (2) A11

Hi word, I must be in front of the 3 in the pattern because in the mold is in 3
Similarly for each of the other options, 1, 2 and 4 of the mold must be in the pattern

Total macro:

Code:

Sub Patrón_Quiz ()
'It work well
fila = 11
for col = 3 a 12
Hoja2.Cells (fila, 1) = Hoja4.Cells (10, col)
'Hoja2.Cells (fila, 1) .Interior.Color = vbYellow
fila = fila + 7
Next  col

'Coloca el Número de preguntas de las 10 del patron aleatorio construido
fila = 11
for col = 16 a 61 step 5
Hoja2.Cells (fila, 4) = Hoja4.Cells (10, col)
'Coloca las opciones
Hoja2.Cells (fila + 1, 4) = Hoja4.Cells (10, col + 1)
Hoja2.Cells (fila + 2, 4) = Hoja4.Cells (10, col + 2)
Hoja2.Cells (fila + 3, 4) = Hoja4.Cells (10, col + 3)
Hoja2.Cells (fila + 4, 4) = Hoja4.Cells (10, col + 4)
'Hoja2.Cells (fila, 1) .Interior.Color = vbYellow
'Hoja2.Cells (fila, 1) .Interior.Color = vbGreen
fila = fila + 7
Next  col
End Sub

Sub ReorganizarPop ()
r = 11
for i = 16 a 61 step 5
buscnum = Hoja4.Cells (10, i)
Set C = Hoja2.Columnas (29) .Find (What: = buscnum, LookIn: = xlValues, LookAt: = xlWhole, SearchDirection: = xlNext)
if C Is Nothing Then
MsgBox "'op' inexistente".
Exit Sub
end if
'Para el texto del tipo de pregunta ... ??
Hoja2.Cells (r, 3) = C.Offset (0, -3) 'Para el texto de las preguntas
'para el texto de las opciones de respuesta
Hoja2.Cells (r + 1, 3) = Split (C.Offset (1, -3), "") (0) & "" & Hoja2.Cells (r + 1, 4)
Hoja2.Cells (r + 2, 3) = Split (C.Offset (2, -3), "") (0) & "" & Hoja2.Cells (r + 2, 4)
Hoja2.Cells (r + 3, 3) = Split (C.Offset (3, -3), "") (0) & "" & Hoja2.Cells (r + 3, 4)
Hoja2.Cells (r + 4, 3) = Split (C.Offset (4, -3), "") (0) & "" & Hoja2.Cells (r + 4, 4)

r = r + 7
Next i
End Sub

That is, I think that, no split is required, as recommended
Maybe it's a new match on sheet2 that is required, but I do not know how to accommodate it,, the code..

Chihiro · Jan 23, 2018

Sorry. But I'm confused as to what really is your requirement.

Your first process (Sub Patron_Quiz) copies info from "Nomina" sheet to "Test" sheet in Column D.

Then you are searching from "Test" sheet Column 29 and putting (Col - 3) value to Column C...

However, pattern from "Nomina" sheet does not match what's in Column 29 (or in 26), so that's where your mismatch is coming from.

What is it that you want to do? You want to fill Col C with Col Z value, but with corresponding position to what's in Col D?

Visor · Jan 23, 2018

You want to fill Col C with Col Z value, but with corresponding position to what's in Col D?

Exactly!!

Because the correct answer will always be the one with the green fill of the pattern and the letter "x" next to it

Well what is in the area from X10 on sheet2 (Test) serves as a template to keep the text of the questions and their order between question and question number and answer options and option number (This will remain fixed).
In the area in column A-D they will be reordered according to the pattern of the Nomina sheet.

Note: If there had been a different way to reorganize the questions and options without using the Artifice of Area X10 it would be interesting. But since I do not know much about macros, I found it more convenient to back up the questions in Area X10 and from there move them according to the pre-established pattern in the Nominee Sheet

However I can only make it work up to what you have already seen and only for the first student

Chihiro · Jan 23, 2018

Then try something like...

Code:

'para el texto de las opciones de respuesta
Hoja2.Cells(r + 1, 3) = Application.Index(C.Offset(1, -3).Resize(4), Application.Match(Hoja2.Cells(r + 1, 4), C.Offset(1, -2).Resize(4), 0))
Hoja2.Cells(r + 2, 3) = Application.Index(C.Offset(1, -3).Resize(4), Application.Match(Hoja2.Cells(r + 2, 4), C.Offset(1, -2).Resize(4), 0))
Hoja2.Cells(r + 3, 3) = Application.Index(C.Offset(1, -3).Resize(4), Application.Match(Hoja2.Cells(r + 3, 4), C.Offset(1, -2).Resize(4), 0))
Hoja2.Cells(r + 4, 3) = Application.Index(C.Offset(1, -3).Resize(4), Application.Match(Hoja2.Cells(r + 4, 4), C.Offset(1, -2).Resize(4), 0))

Visor · Jan 23, 2018

Yes!!!

Thanks Chihiro, it works!!

I was trying according to the recommendation of MarcL placing left but surely nothing happened to me, I will continue to learn from you great Masters of the vba macros

Then with find but never, I was never going to consider a solution like the code you gave me.
At last I can explain correctly.
This deserves a toast in the distance, for my part I'll have something to drink, I'm happy

Now I will do it to work according to the selection in the combo box, I do not want to take more time from you

It is but sorry for the inconvenience

I am very happy with the solutions

create student questionnaire selected in combobox based on question pattern

Visor

Member

Attachments

Visor

Member

Marc L

Excel Ninja

Visor

Member

Marc L

Excel Ninja

Visor

Member

Marc L

Excel Ninja

Visor

Member

Marc L

Excel Ninja

Chihiro

Excel Ninja

Visor

Member

Visor

Member

Chihiro

Excel Ninja

Visor

Member

Chihiro

Excel Ninja

Visor

Member