Count number of times entries are made in a cell

[MichaelRLanier]

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.

 

[Jim Thomlinson]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[MichaelRLanier]

Thanks Jim and Rick. Your help is much appreciated. I'll be trying
both approaches shortly.