function for counting how many times a Q is answered wrong

[Guest]

Hi there,
I'm a bit stuck - I've written a fairly simple S/sht which is basically
designed to hold the answers to a number of book based multi choice tests.
Reason behind it was simply so that the student can enter the answer they
think is appropriate without either marking their book, thus meaning they
can't repeat that test, or without the need to write out the question numbers
repeatedly.

So, the actual comparison and "right or wrong" flag is fairly simple, but
one extra bit of functionality I'd like to add is to add a running count of
how many times a particular question has been answered incorrectly....so that
hopefully the tests can be done several times and attention diverted to those
topics causing the most problem.

My issue is I don't know how to do this, as the standard worksheet functions
would reset to zero when the original answer is deleted....I think it might
be a macro job, but wouldnt' know where to start....any help gratefully
received!

 

[Bernie Deitrick]

Charly,

You could use the an event to record results and to clear the results table when you want to reset.

For example, if the student enters answers into column B, and you have the Right or Wrong formulas
in column C, then this code will keep a running count of how many times the answer has been answered
incorrectly in column H. Copy the code, right click the sheet tab, select "View Code" and paste the
code into the window that appears.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$D$1" And UCase(Target.Value) = "CLEAR" Then
Application.EnableEvents = False
Range("H:H").ClearContents
Application.EnableEvents = True
Exit Sub
End If
If Target.Column <> 2 Then Exit Sub
If Target.Offset(0, 1).Value <> "Wrong" Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row, "H").Value = Cells(Target.Row, "H").Value + 1
Application.EnableEvents = True
End Sub

To clear the record, type "clear" (without the quote marks) into cell D1 of the same sheet.

If you have trouble changing this code to reflect your actual sheet structure, then post back.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie,

Bit of fiddling around and that's worked an absolute treat! Thank you so
much!

 

[Bernie Deitrick]

Charly,

You're welcome, and thanks for letting me know that you got it to work.

Bernie
MS Excel MVP