asked on

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

Tracy

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

ASKER CERTIFIED SOLUTION

ScottMorris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tracy

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.

ZacharyDG

ASKER

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.

ScottMorris

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

ZacharyDG

ASKER

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.

ScottMorris

Did you find a way to have the code run when your data updates?

ZacharyDG

ASKER

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.