Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count number of times entries are made in a cell
Is it possible to count the number of times a cell's content is
changed? For example, if an entry is made in cell A1, then later deleted and re-entered, is there a macro that will tell me the cell has had 2 entries total? Thanks for your help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count number of times entries are made in a cell
Right click the sheet tab you want this to work in and paste the following
code... It works on the cells in A1:A10. Change that to suit... Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim rngChanged As Range Set rngChanged = Intersect(Target, Range("A1:A10")) 'Change range If Not rngChanged Is Nothing Then Application.EnableEvents = False On Error Resume Next For Each rng In rngChanged rng.Offset(0, 1).Value = rng.Offset(0, 1).Value + 1 Next rng On Error GoTo 0 Application.EnableEvents = True End If End Sub -- HTH... Jim Thomlinson " wrote: Is it possible to count the number of times a cell's content is changed? For example, if an entry is made in cell A1, then later deleted and re-entered, is there a macro that will tell me the cell has had 2 entries total? Thanks for your help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count number of times entries are made in a cell
I might change this line...
rng.Offset(0, 1).Value = rng.Offset(0, 1).Value + 1 to this instead.... If rng.Offset(0, 1).Value < "" Then rng.Offset(0, 1).Value = rng.Offset(0, 1).Value + 1 End If That way, deletions will not be counted (I think that is what the OP was hinting at in his next-to-last sentence). Rick "Jim Thomlinson" wrote in message ... Right click the sheet tab you want this to work in and paste the following code... It works on the cells in A1:A10. Change that to suit... Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim rngChanged As Range Set rngChanged = Intersect(Target, Range("A1:A10")) 'Change range If Not rngChanged Is Nothing Then Application.EnableEvents = False On Error Resume Next For Each rng In rngChanged rng.Offset(0, 1).Value = rng.Offset(0, 1).Value + 1 Next rng On Error GoTo 0 Application.EnableEvents = True End If End Sub -- HTH... Jim Thomlinson " wrote: Is it possible to count the number of times a cell's content is changed? For example, if an entry is made in cell A1, then later deleted and re-entered, is there a macro that will tell me the cell has had 2 entries total? Thanks for your help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count number of times entries are made in a cell
Thanks Jim and Rick. Your help is much appreciated. I'll be trying
both approaches shortly. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count the number of times a cell value is within a specific range | Excel Worksheet Functions | |||
How do I count the number of times a letter is used in a cell? | Excel Discussion (Misc queries) | |||
count the number of cell entries after filtering | Excel Discussion (Misc queries) | |||
How do I count the # of times an entry has been made in a month? | New Users to Excel | |||
Count number of times a specific number is displayed in a cell ran | Excel Worksheet Functions |