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.

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.

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.

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.