Making a cell count the amoun of entries that has been put into it

[Pawpaw]

I use Excel to teach my grandson math. In column A is a number. In
column B he has to type a number. In column C is the formula "=a1/a2" If he
types the correct number in column B the correct answer will appear in column
C. I also have the correct answer listed in column D. His instructions are to
type the correct number in column B and if he does then the answer in column
C will match the number in column D.
Now what I would like to do is to be able to have a column that counts
the number of times he types a number in column B so that I will know that he
is actually doing the work and not just guessing by typing numbers in column
B until he gets the correct answer.

 

[Bernie Deitrick]

Pawpaw,

Copy the code below, right-click the sheet tab, select "View Code" and paste
the code into the window that appears. The code will count the number of
tries in column B, recording the number in column Z of the same row, out of
sight and so, perhaps, un-noticed by your grandson. The second version (you
can only use one) will record his entered values starting in column AA and
on to the right, so that you can see what he did in each cell.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row, 26).Value = Cells(Target.Row, 26).Value + 1
Application.EnableEvents = True
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row, 26).Value = Cells(Target.Row, 26).Value + 1
Cells(Target.Row, Columns.Count).End(xlToLeft).Cells(1,2).Value =
Target.Value
Application.EnableEvents = True
End Sub

 

[Pawpaw]

Thank you so much Mr. Deitrick, it worked perfectly.

I hate to bother you again. You are probably very busy so I will
understand if you do not have the time. I neglected to mention that I also do
the same thing with columns F, G. and H. In column F is a number. In column G
he has to type a number. In column H is the formula "=a1/a2" If he types the
correct number in column G the correct answer will appear in column H. I also
have the correct answer listed in column I. His instructions are to type the
correct number in column G and if he does then the answer in
column H will match the number in column I.
Now what I would like to do is to be able to have a column that counts
the number of times he types a number in column G so that I will know that he
is actually doing the work and not just guessing by typing numbers in column
G until he gets the correct answer.
Mr. Deitrick if you could write another formula just like the one you
wrote earlier that placed the answer in column Z, but this time have the
answer placed in column AA or if you could explain to me the formula I would
be able to write it myself. Either way I will appreciate whatever you can do.
Thank you again for your time.

 

[Bernie Deitrick]

Pawpaw,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 2 And Target.Column <> 7 Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row, IIf(Target.Column = 2, 26, 27)).Value = _
Cells(Target.Row, IIf(Target.Column = 2, 26, 27)).Value + 1
Application.EnableEvents = True
End Sub

HTH,
Bernie
MS Excel MVP

 

[Pawpaw]

Thank you so much Mr. Deitrick it worked perfectly.

 

[Bernie Deitrick]

You're very welcome. Good luck with your grandson.

Bernie
MS Excel MVP