+ Reply to Thread
Results 1 to 14 of 14

Help with VBA code to calculate changes made in Column

  1. #1
    Registered User
    Join Date
    10-16-2023
    Location
    Los Angeles, CA
    MS-Off Ver
    Office 365
    Posts
    9

    Help with VBA code to calculate changes made in Column

    Hello all you geniuses. I have another VBA issue. I have built a sheet, and what I'm looking for is when anyone changes the date in column J (Last Update), it counts that as updated in cell F3 (Actions updated since last review). I have the VBA code as:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Sheets("Sheet1").Range("J10:J136"), Target) Is Nothing Then
    Range("F3").Value = Range("F3").Value + 1
    End If
    End Sub

    However, what I'm realizing now is I need F3 to reset to 0 every time the worksheet is opened, so that it's only counting the current updates and doesn't continue to increase the count of total updates made.

    Thanks in Advance
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,466

    Re: Help with VBA code to calculate changes made in Column

    Try this:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    10-16-2023
    Location
    Los Angeles, CA
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Help with VBA code to calculate changes made in Column

    Thanks Trevor. This certainly worked. Now a sub question as it relates to this setup; If I expand the workbook to, lets say 20 sheets, all identical but for different offices, this will reset f3 to 0 on all of them correct? If I wanted it to be sheet specific but not apply to the workbook as a whole, is there a way to accomplish that?

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,466

    Re: Help with VBA code to calculate changes made in Column

    As it stands, the Workbook Open Code will reset F3 to zero on the Active Sheet when the workbook is opened. And that will depend on which sheet is selected when the workbook is closed.

    You'd need:

    Please Login or Register  to view this content.
    This would need to be repeated in each sheet where applicable:

    Please Login or Register  to view this content.
    Or, you could use this in the ThisWorkbook module but you would need refer to the sh variable.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    10-16-2023
    Location
    Los Angeles, CA
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Help with VBA code to calculate changes made in Column

    Thanks for the help. I've uploaded a new file with 3 sheets in the workbook, when I use the code now nothing updates. Maybe I'm missing something, but I went through the lines in the code, and everything seems right.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,215

    Re: Help with VBA code to calculate changes made in Column

    Works OK for me: if I update J10 then F3 is updated.

    If I close and re-open the w/book F3 counts are zero.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  7. #7
    Registered User
    Join Date
    10-16-2023
    Location
    Los Angeles, CA
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Help with VBA code to calculate changes made in Column

    Yes, it does seem to be working, thanks John. I erased all the code and re-entered it. It is working as described now. However, I'd also like a different option, maybe this needs a new post. What about having a sheet update the count in F3 only when a date is changed in column J. So basically if there is a 2 in F3, and someone changes a date in column J, then it would reset to a 1. If 3 dates are changed in column J then F3 would show a 3. Does that make sense?

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,215

    Re: Help with VBA code to calculate changes made in Column

    No .. I do not understand the difference in the scenarios you describe

    What about having a sheet update the count in F3 only when a date is changed in column J.
    this is the current logic. Or are you differentiating between change to an EXISTING date and a NEW date?
    Last edited by JohnTopley; 12-21-2023 at 03:07 PM.

  9. #9
    Registered User
    Join Date
    10-16-2023
    Location
    Los Angeles, CA
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Help with VBA code to calculate changes made in Column

    What I am trying to accomplish is; lets say there is a "3" in F3 on Sheet1. Now if someone goes in and views sheet1 but doesn't make in any changes, it stays as a 3. However, if somechanges 1 of the dates in column J, then F3 would show a 1. If someone changes 4 dates in column J, then f3 would show a 4. So basically it wouldn't reset to 0 ever.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,215

    Re: Help with VBA code to calculate changes made in Column

    How are you going to differentiate between changing a date and re-setting F3 to 1 vs entering 4 (or any number of) dates: why should this latter action not reset F3 to 1?

  11. #11
    Registered User
    Join Date
    10-16-2023
    Location
    Los Angeles, CA
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Help with VBA code to calculate changes made in Column

    I'm only looking for F3 to change by how many dates are changed in column J. So, if there are 6 line-items and someone only changes dates for 2 of them, then F3 would show a 2. If someone changes 5 of the line-item dates then a 5 would show in F3. It'll always vary depending on who is going in and how many items they update at that time.

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,466

    Re: Help with VBA code to calculate changes made in Column

    Is this going to be a shared workbook? Shared workbooks and VBA are notoriously bad at playing well together.

    If it's not, only one person can open and edit the workbook hence every time the workbook is opened, the counter will reset. Only the current user will see the counter increase.

    Personally, I would have a log sheet and use the Worksheet Change Event handler to record all date changes in it. You could include date, time, sheet name, cell reference, old value, new value, and user ID.

  13. #13
    Registered User
    Join Date
    10-16-2023
    Location
    Los Angeles, CA
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Help with VBA code to calculate changes made in Column

    Yes it'll be a shared workbook. The people that will be updating it and the people that will be looking at it will be different. Basically, higher ups will view the sheets to see how things are progressing (and who isn't), managers will need to go in and update as needed. I could always have the managers update F3 manually with how many they updated, I was just trying to avoid that step if possible. So having the change log creates an extra step that the higher ups want to avoid.

  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,466

    Re: Help with VBA code to calculate changes made in Column

    OK. The code you've been given should do what you originally asked for. I don't understand the follow up question so I'm not going there.

    I suggest youu destruction test the workbook/code before you share with management.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculate pricing according to selections made
    By wezelo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-25-2022, 09:36 PM
  2. calculate if a car has made a return journey
    By rikkyshh in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-16-2019, 07:18 AM
  3. [SOLVED] How do I calculate rate when pmt is made at end of periods
    By Ron Coderre in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 07:05 PM
  4. [SOLVED] How do I calculate rate when pmt is made at end of periods
    By hsummer in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  5. How do I calculate rate when pmt is made at end of periods
    By hsummer in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  6. [SOLVED] How do I calculate rate when pmt is made at end of periods
    By hsummer in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  7. [SOLVED] How do I calculate rate when pmt is made at end of periods
    By hsummer in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1