• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

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
0
ZacharyDG
Asked:
ZacharyDG
  • 3
  • 3
  • 2
1 Solution
 
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
 
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now