Count?

[Guest]

I would like to know if I there is a way to count the number of times a value
in a cell changes. I have a spreadsheet that reads in data from a "PLC" and
the data changes throughout the day, what I am looking for is a way to count
the times that "value" in the cell changed. Thanks

 

[Steve Yandl]

Here is one option.

For the example, I'm looking for how many times the value in cell C5
changes. The count appears in cell G2 and I've used cell G1 to store the
last changed value from C5 so there is a reference for comparison, if and
when the value of C5 is changed. The code goes in the worksheet, not a
module.

'__________________________________________________

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Intersect(Target, Me.Range("C5")) Is Nothing Then
If Not Target.Value = Me.Range("G1").Value Then
Me.Range("G2").Value = Me.Range("G2").Value + 1
Me.Range("G1").Value = Target.Value
End If
End If

Application.EnableEvents = True

End Sub

__________________________________________________

Steve

 

[Mike Fogleman]

As a worksheet -change event this will count how many times cell A2 was
updated and put the count in B2. This is different from your statement
"count the number of times a value in a cell changes." In other words, it
does not compare the new value to the old value or any previous value. If
cell A2 was updated 4 times, but each time the value remained the same, it
will give you the count of 4, even though the number value of A2 never
changed. Is that what you want?

Option Explicit
Public counter As Long

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
counter = counter + 1
Target(1, 2).Value = counter
Exit Sub
End If
End Sub

Put this code in the Worksheet code module.
Mike F