Excel Event Counter

I need a program that will count the number of times a number in a row exceeds or is less than a certain value.  The numbers in the target column are DDE linked and will be constantly changing.  I need to be able to count every time the value in the target column exceeds or is less than a specified value in the adjacent column.  For example, if the number in column A exceeds the number in Column B, Column C will display a "1".  If the number in column A changes and goes below the number in column B, then column D will display a 1 and Column C will still display a 1.  If the number in column A goes back above the value in column B, then Column C will display a 2 and column D will display a 1 and so forth.  Furthermore, I will need to run this program for two sets of cells, ABCD and EFGH.  Thank you and let me know if you have any questions.
-Z
ZacharyDGAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TracyVBA DeveloperCommented:
Try this:
Sub myCounter()

    Dim i As Long
    Dim lastRow As Long
    Dim tempCol As Integer
    
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    tempCol = 2

    For p = 1 To 2
        For i = 2 To lastRow
            If Cells(i, tempCol).Value <> Cells(i, tempCol - 1).Value Then
                Cells(i, tempCol + 1).Value = Cells(i, tempCol + 1).Value + 1
                Cells(i, tempCol + 2).Value = 1
            Else
                Cells(i, tempCol + 2).Value = 0
            End If
        Next i
        tempCol = tempCol + 4
    Next p
    
End Sub

Open in new window

Book1.xls
0
ScottMorrisCommented:
@broomee9:

Your code increments the counter column every time the code is run and resets the "change" column under certain circumstances.  I read the request slightly differently and made a script that runs on worksheet_update.  See the attached file for an example.
25589817.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TracyVBA DeveloperCommented:
Yes, mine should be ran each time the sheet is updated.  Yours works too.  Mine updates all at once, yours updates everytime a change is made.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

ZacharyDGAuthor Commented:
Scott's model is more of what I was looking for; however, I need two changes to be made.  First, the counter cell cannot be updated every time the cell in column A changes.  For example, if column A is 12 and column B is 10, column C will count 1.  If I change column A to 13, column C will count 2.  I only want column C to update when the value in column A goes below the value in column B and then goes back above (See my original description).  Furthermore, since these cells will be updated automatically and I will not be pressing enter to change the cell values, I need the counter to work automatically.  Let me know if you have any questions.
0
ScottMorrisCommented:
I've made an adjustment to the sheet to save the last action per value set.  Hopefully the additional column will not interfere with how your original file works.  In terms of having to press enter, it may or may not work anyways (depending on how the values are being updated) so give it a shot.  If you need to change it to only run on a button press, you should be able to move the code around easily enough.

I've also changed the code around a little bit.  It's a little bit fuller, but I wanted to make it a little more readable.
25589817-2.xls
0
ZacharyDGAuthor Commented:
The formula works great; however, it is still unable to recognize changes in the cells in column A if I do not press enter.  The cells are linked to a stock trading platform and the cells will be automatically changing as stock prices change.  Do you know how to alter this formula so that it will recognize when the cells change without having to actually enter new values?  Otherwise, the program works exactly as I had hoped.
0
ScottMorrisCommented:
Did you find a way to have the code run when your data updates?
0
ZacharyDGAuthor Commented:
No, but I created a new post entitled "Timestamp/Counter" with a request that expands upon your original code.  Maybe take a look at that one and let me know if you or anyone you know can answer it; its a fairly complex question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.