Count the number of changes to a cell
 

Is there a function to keep track (count) of how many times a particular
cell, the date in the cell, has changed? Thanks!!!

R

Count the number of changes to a cell
 

It's possible and is fairly complicated but may not be practical in most
applications.

http://www.oaltd.co.uk/Excel/Default.htm

Look for Previous.zip

--
Biff
Microsoft Excel MVP


"RMan" wrote in message
...
Is there a function to keep track (count) of how many times a particular
cell, the date in the cell, has changed? Thanks!!!

R

Count the number of changes to a cell
 

Event code can do this.

Enter a 0 in C1 then copy/paste this code into your worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo enditall
Application.EnableEvents = False
If Target.Value < "" Then
With Target.Offset(0, 2)
.Value = .Value + 1
End With
End If
enditall:
Application.EnableEvents = False
End Sub

Right-click on the sheet tab and "View Code"

Paste into that sheet module.

Whe you change A1 C1 will add to the count each time.


Gord Dibben MS Excel MVP

On Wed, 3 Oct 2007 13:00:00 -0700, RMan wrote:

Is there a function to keep track (count) of how many times a particular
cell, the date in the cell, has changed? Thanks!!!

R

Count the number of changes to a cell
 

Gord, I couldn't get this to work properly.

It only counted a change once. The counter cell went from 0 to 1 and that's
it!

You left events disabled at the end. Even turning events back on didn't make
a difference.

--
Biff
Microsoft Excel MVP


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Event code can do this.

Enter a 0 in C1 then copy/paste this code into your worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo enditall
Application.EnableEvents = False
If Target.Value < "" Then
With Target.Offset(0, 2)
.Value = .Value + 1
End With
End If
enditall:
Application.EnableEvents = False
End Sub

Right-click on the sheet tab and "View Code"

Paste into that sheet module.

Whe you change A1 C1 will add to the count each time.


Gord Dibben MS Excel MVP

On Wed, 3 Oct 2007 13:00:00 -0700, RMan
wrote:

Is there a function to keep track (count) of how many times a particular
cell, the date in the cell, has changed? Thanks!!!

R

Count the number of changes to a cell
 

Works for me.

Enter 0 in C1 and add something to A1

Change A1 and number in C1 increases for me each time a change is made.

Thanks for pointing out the not turning avents back on. Damn copy and paste<g


Gord

On Fri, 5 Oct 2007 02:45:25 -0400, "T. Valko" wrote:

Gord, I couldn't get this to work properly.

It only counted a change once. The counter cell went from 0 to 1 and that's
it!

You left events disabled at the end. Even turning events back on didn't make
a difference.

--
Biff
Microsoft Excel MVP


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Event code can do this.

Enter a 0 in C1 then copy/paste this code into your worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo enditall
Application.EnableEvents = False
If Target.Value < "" Then
With Target.Offset(0, 2)
.Value = .Value + 1
End With
End If
enditall:
Application.EnableEvents = False
End Sub

Count the number of changes to a cell
 

Well, I must have screwed up something when I first tried this!

It works now. Much better than the method I referenced.

--
Biff
Microsoft Excel MVP


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Works for me.

Enter 0 in C1 and add something to A1

Change A1 and number in C1 increases for me each time a change is made.

Thanks for pointing out the not turning avents back on. Damn copy and
paste<g


Gord

On Fri, 5 Oct 2007 02:45:25 -0400, "T. Valko"
wrote:

Gord, I couldn't get this to work properly.

It only counted a change once. The counter cell went from 0 to 1 and
that's
it!

You left events disabled at the end. Even turning events back on didn't
make
a difference.

--
Biff
Microsoft Excel MVP


"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
Event code can do this.

Enter a 0 in C1 then copy/paste this code into your worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo enditall
Application.EnableEvents = False
If Target.Value < "" Then
With Target.Offset(0, 2)
.Value = .Value + 1
End With
End If
enditall:
Application.EnableEvents = False
End Sub