VBA Scoring Test Answers

[Stephen_IV]

Can someone please help me. I am looking for some vba to automatically score some answers that I have for questions. The questions are from 1 to 5. I would like VBA to give the the TOTAL score right for each person. Can someone please help me.

Thanks in advance Stephen.

Book1
	A	B	C	D	E	F	G	H	I	J
1		Answer1	Answer2	Answer3	Answer4	Answer5	Answer6	Answer7		TotalScore
2	Steve	2	4	5	2	2	3	2		4
3	Bob	3	4	1	2	3	3	2		5
4	John	1	1	1	2	4	3	5		3
5	Mike	5	5	4	2	3	1	3		2
6	Ray	4	2	4	2	1	5	3		2
7	Tony	5	2	1	1	1	2	2		4
8	Sam	3	4	2	1	1	5	5		2
9	Tom	5	4	5	4	1	2	3		3
10
11
12
13
14		Score
15	Answer1	5
16	Answer2	4
17	Answer3	1
18	Answer4	2
19	Answer5	1
20	Answer6	3
21	Answer7	2
Sheet1

 

[Zack Barresse]

what determines wether the answer is right or not?

 

[Stephen_IV]

Actually,
The numbers that are below from B15:B21. Which are entered in by myself. It would also be OK if they had to be entered into VBA for the problem to work!

 

[PaddyD]

still not clear how this is all being worked out...

 

[tactps]

If I read it correctly, the object is to see if cell b2 = b15, c2 = b16, etc to determine how many of the answers in row 2 match the corresponding cell in b15:b21.

I would use:
=COUNTIF($B2,$b$15)+countif($C2,$b$16)+...

but I'm sure you can come up with a shorter formula than me Paddy/Zack!

 

[Stephen_IV]

OK

In this example there are 7 questions per each person. Each person took a test seperatly. What I have done is enter the answers into excel. The right answers per each question are located in B15:B21. So qeustion 1 the right answer would be 5, question 2 would be 4, question 3 would be 1 and so forth. Steve answered question 1 with an answer of 2 which would not be right because the answer to question 1 was 5. So what I would lik is a tally of the how many correct answers each person had. I would preferably like this done in VBA if possible. I hope that I am making myself clear. Sorry if I am not.
Steve

 

[PaddyD]

see the example:

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Answer1	Answer2	Answer3	Answer4	Answer5	Answer6	Answer7	TotalScore
2	Steve	2	4	5	2	2	3	2	4
3	Bob	3	4	1	2	3	3	2	5
4	John	1	1	1	2	4	3	5	3
5	Mike	5	5	4	2	3	1	3	2
6	Ray	4	2	4	2	1	5	3	2
7	Tony	5	2	1	1	1	2	2	4
8	Sam	3	4	2	1	1	5	5	2
9	Tom	5	4	5	4	1	2	3	3
10
11	Answers
12	Answer1	5
13	Answer2	4
14	Answer3	1
15	Answer4	2
16	Answer5	1
17	Answer6	3
18	Answer7	2
Sheet3

the formula in i2 is:

=SUMPRODUCT(($B$1:$H$1=$A$12:$A$18)*(B2:H2=$B$12:$B$18))

...which is then copied down. note the mix of absolute / relative references.

 

[Stephen_IV]

Thanks Paddy. I appreciate your time and help on this situation. Thanks Again!

 

[PaddyD]

You're welcome!

 

[Stephen_IV]

Althought Paddy D did give me a formula answer I am still looking for a VBA approach can anyone please still help me?