Counting events in a dynamically linked cell.

count loop

I don't know the program. but are you able to use a count loop?

Here's one way to do it

Let's say you want to monitor the changes in value to cell A1, and you want to know the date and time of thise changes.

To create such a record, right click on your sheet tab, left click on View Code, and paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Range("B65536").End(xlUp).Offset(1, 0).Value = Target
Range("C65536").End(xlUp).Offset(1, 0).Value = Format(Now(), "mm/dd/yy" & " " & "hh:mm:ss")
End Sub

Format column B as General.

From here, you could write code to view changes within intervals of seconds, minutes, hours, days, etc., and with what values as they changed.

Any help?

Tom Urtis

Re: Here's one way to do it

Using Worksheet_changed is dicey because it triggers on absolutely anything that is done to the sheet. How about writing a function (VBA/Insert Module) that just adds up the toggles in a global variable.
Dim s_nToggles as integer
dim s_nOldValue as integer
public function toggles(nInput as Integer)
if nInput <> s_nOldValue then
s_nOldValue = nInput
s_nToggles = s_nToggles+1
Toggles = s_nToggles
endif
end function

Then put =toggles(a1) in any cell.

Not sure that would work in this case...

Hi, thank you for the suggestion, but I see 2 problems with that:

(1) Contrary to what you said, this would not trigger based on "absolutely anything that is done to the sheet". The event is only triggered on a change to one cell, A1 as specified.

(2) What was requested was a running record of changes in value to a cell over time, which my code provides. Your suggestion is for a UDF, which is a formula to be entered into a cell, but what about all the other occurrences needing to be recorded in that minute and beyond (up to 50 or so instances per minute according to the original post).

If I am way off base here please tell me why; my interpretation of the original post and/or your suggestion might well be incorrect.

Tom Urtis

Re: Not sure that would work in this case...

Well, not way off base, but regarding (1), your code is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
....
The fact that you're checking the address means that the event fires for any address. I agree that the rest of the code only runs in the case of A1.
Regarding (2), the request was "I need a script or macro to count the changes from 0 to 1", so I just do a count, not a record. I agree you provide a record which goes a little beyond the request. (Granted - it would be the *next* request). For my solution to provide a record a considerable amount of fooling with global variables would be required.

Re: Not sure that would work in this case...

------------------------------------------------

I want to thank both of you for the great job each
did in solving this problem. Both Procedures are
now running in the plant and gathering data. We just decided to use both of them.
It Sure did help Tom! As a matter of fact, it does some other things that will be good for us
It maintains a running list of the events..which is good. I had to create a formula to count
the number of transitions in B1:B65536, and then divide and Roundup the value. Then, each
time a transition from 0 to 1 and back to 0 was made, it added one to the count. That's what
we needed. We also got the extra added benefit of having a running list of each transition.

The Function from the other reader( Gerlemph) also worked after massaging the function result
with a formula similar to the one I spoke of earlier. With his, I am still trying to get the counter
to reset to zero whenever I need it to. It worked real slick also.

I thank both of you for the help and it has prompted me to go to Hastings Book store and buy a Visual
Basic book. I am going to try hard to learn it in the next year.

Thank you both.

Charles Pearce