Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count?
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 "Mark J" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count?
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 "Mark J" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
Excel Count characters in a textbox to display character count? | Excel Programming | |||
Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |