Posts by btwice

    Sorry, not the most experienced with VBA yet :( but I've commented where I could, and separated out the variables to what I think they should be, let me know if this is a little better to figure out.

    Originally this process was for another group to do and was meant for keeping a running track of the jobs that were updated by them, but without duplicates, however I'll be running the process myself so there won't be 2 tables.

    Yes this is part of the same workbook, but was a different issue of the code updating values for something that didn't exist, so I made a different thread, I can reply to the other thread with this if that is a better way to do it.

    I know you don't understand why I am doing it this way, but was still curious as to why the code is dropping a value in when it doesn't find a match.

    I'm using code to update data in the blue table based on values in the orange table when the "Order" and "Notification" combination matches. It works except it is adding the last row of values from the orange table to the blue table, even if it doesn't find a match.

    I've posted the file with the code I'm using inside, one click of the button will show what I mean here. I only want it to update when it finds a match, blank cells shouldn't count toward this.

    The purpose is to keep a running list of data that is updated, and occasionally added to. Data is being taken from one sheet, pasted to the table on the right in this example, and then used to update the table on the left, or add to it if the values don't exist. The table on the left is referenced on another sheet to indicate changes that need to be made.


    I'm trying to run some VBA to do a few things all at once. There are 3 parts, one I have working correctly which updates data that exists in the sheet already, and 2 I need help with which I've listed below. I attached the workbook for reference, but the process would be to paste data into the table on the right side of the sheet, press the 'confirm click updates' button, and it will:

    A) update data in the table on the left based on order/notification matches (this I have working already)

    B) paste order/notification combinations and their data in the first empty row of the left table if they don't exist in the table on the left (this sort of works, but it puts data in the first empty row at the bottom of the table, rather than just the first row that doesn't contain data)

    C) (this I don't have working at all) Count when "revised click start date" is changed by the VBA code, and count when "revised click end date" is changed by the VBA code independently of each other, and place those counts in the respective columns given the conditions below

    1. Do not update the count if the change is from null to a value

    2. Do not update the count if the change is from the value that exists to the same value

    Please let me know if any clarification is needed on this, thanks.

    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.

    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?

    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.

    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.

    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.


    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.

    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

    Sure, the main workbook has become pretty robust, so I've parsed out what I'm having trouble with to this workbook which contains the button/code and some sample data. If you notice on the tracker sheet, there are duplicate Order numbers, but the current code only updates the first match that it finds and moves to the next unique order number and updates that.

    What I would like it to do, is update every instance of the order number, rather than just the first match it finds. Does this make sense?