Count times a cell is changed within a column

  • Hi,


    I have this piece of code that will increment a count by +1 every time the adjacent cell is changed, but I want to make this work for an entire column rather than just 1 cell. Would an altered version of this code be the way to go (one that uses ranges such as A2:A instead of direct cell references), or will this code cause Excel to bog down a large amount? Thanks.


    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. '// Changed cell is A1?? Change to suit
    3. If Not Intersect(Target, Range("A1")) Is Nothing Then
    4. '// Increment tracker cell- Again, change to suit
    5. Range("C1").Value = Range("C1").Value + 1
    6. End If
    7. End Sub
  • Hi,


    You can test following macro


    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Target.CountLarge > 1 Then Exit Sub
    3. If Target.Column <> 1 Then Exit Sub
    4. Target.Offset(0, 2).Value = Target.Offset(0, 2).Value + 1
    5. End Sub

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • This works, but how could I run this for 2 separate columns in the same sheet? So check column C for changes and populate the count in column E, and also check column D for changes and populate that count in column F.

  • Hello,


    Below is the requested modification


    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Target.CountLarge > 1 Then Exit Sub
    3. If Intersect(Target, Range("C:D")) Is Nothing Then Exit Sub
    4. Target.Offset(0, 2).Value = Target.Offset(0, 2).Value + 1
    5. End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hm, I guess I should have been more detailed haha. I thought my example might be enough but guess I'll just go with what I'm actually going for. I need to check column V, and also column X independently for changes (not adjacent to each other, and if V changes its count goes up, but X count does not, and vise versa), then put the counts in 2 different columns (count for V goes in column AB, count for X goes in AC).


    There's one other condition I noticed I have to apply to this, I don't want the count to update if the cell changes from blank to something, or if it changes from the same value it contains (1 changing to 1 is technically a change, but same value). Let me know if this all makes sense here.

  • In order to make things easier ... and clearer ... why don't you attach a sample file ...


    This will allow you to illustrate your objective and make it totally visible ...:)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Sure, see attached. The "Sac Valley Contractor" sheet is where values will be updated in columns V and X, and I want to count the changes independently and put the counts in columns AB and AC given the condition that changes are only counted if the cell does not change from blank to something, and does not change from the value that exists in the cell already.

  • Thanks for the sample file


    Below is the Event macro to be tested


    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Target.CountLarge > 1 Then Exit Sub
    3. If Intersect(Target, Range("V:V", "X:X")) Is Nothing Then Exit Sub
    4. Dim x As Integer
    5. If Target.Column = 22 Then x = 6 Else x = 5
    6. Target.Offset(0, x).Value = Target.Offset(0, x).Value + 1
    7. End Sub

    Hope this will solve your problem

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • This works for counting both independently and in the correct columns! However it is still counting up if it goes from blank to a value, and if it changes from the same value. The code makes sense to me and seems like it shouldn't count up but still does.

  • Sorry ... but I think I have not managed to understand your request ...:S


    Would you mind explaining precisely the count you need ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • So everything in your code works correctly, but I need to set 2 conditions for when the code will run, I've listed those below. The only other issue with your code is that when changes are made in column W it updates the count in column AB (think this is because W intersects with V and X?), I only want the code to run when changes are made to V or X.


    Condition 1: Code should not run if a cell is null, and changes to a value

    Condition 2: Code should not run if a cell contains a value, and changes to the existing value (e.g. 1 gets updated to 1, don't run code)


    Does this help at all?

  • Thanks a lot for your clarification ...


    As soon as I have a moment ... will tackle the modifications ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Attached is Version 2 ... to be tested out ...


    Thanks for sharing your comments ... once you have performed your tests ...

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • So this works, although it's still updating the count when column W is changed.


    The other issue is that when i run the "Run Schedule" code on the "Contractor Schedule" sheet which updates these columns the count doesn't update :( sorry I haven't made this easy for you haha. Is it a different event when a cell is updated from VBA? I've inserted the code that runs to update columns V and X here.


  • Quote

    Is it a different event when a cell is updated from VBA ?


    Absolutely ... !!!


    Totally different ... you can completely scrap the Event macro ....:cursing:


    All the various counts you need must be fully integrated into your current RunSchedule macro ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • So sorry! ;( Didn't mean to lead you astray on this, just didn't realize I'd have to integrate this into the existing code that runs, not the most experienced with Excel VBA yet :( should I start a new thread for this request or continue here?

  • Apparently ... you have not created a new thread ...


    Does it mean you would prefer to start all over within this thread ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)