Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a cell count the amoun of entries that has been put into it
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a cell count the amoun of entries that has been put into it
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a cell count the amoun of entries that has been put int
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" wrote: 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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a cell count the amoun of entries that has been put int
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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a cell count the amoun of entries that has been put int
Thank you so much Mr. Deitrick it worked perfectly.
"Bernie Deitrick" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a cell count the amoun of entries that has been put int
You're very welcome. Good luck with your grandson.
Bernie MS Excel MVP "Pawpaw" wrote in message ... Thank you so much Mr. Deitrick it worked perfectly. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using SEARCH in a single cell to COUNT multiple entries of same te | Excel Programming | |||
Using SEARCH in a single cell to COUNT multiple entries of sam | Excel Programming | |||
Using SEARCH in a single cell to COUNT multiple entries of same te | Excel Programming | |||
Count number of times entries are made in a cell | Excel Programming | |||
count the number of cell entries after filtering | Excel Discussion (Misc queries) |