+ Reply to Thread
Results 1 to 10 of 10

Track changes in worksheet change using arrays

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Track changes in worksheet change using arrays

    Hello everyone
    I have written the following code to track any changes in range("C4:D" & lr)
    Please Login or Register  to view this content.
    I don't need to use ranges as helper to do this. Could it be stored in an array then to sort the new entered data and at last to put the new array results to the range
    Hope the code is clear

    Put any data in sheet1 in C4:D8 for example
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Track changes in worksheet change using arrays

    Hello,

    Here's a suggestion using array and the Sort2DVert routine taken from the "Excel 2007 VBA Programming" book. The Sort2DVert is used 3 times in order to simulate a sorting on both columns.

    Please Login or Register  to view this content.
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: Track changes in worksheet change using arrays

    Thank you very much Mr. GC Excel
    I tested the code and tried to type any value in D5 but there is no change ..Is there something wrong with test?

  4. #4
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: Track changes in worksheet change using arrays

    Any idea why it doesn't work in worksheet change as expected?

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: Track changes in worksheet change using arrays

    The first code in post #1 is working well but I need not to rely on ranges to copy data to .. Just depend on arrays

  6. #6
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Track changes in worksheet change using arrays

    Hello Yasser,

    I don't know in which you tested... I've made slight modification to the code so prevent some error.
    However note that I did some tests and in the cases where you have duplicate value it will not work properly. The Sort2Dvert function doesn't work in those case (sorting 2 columns same time) and I don't have the time to update it.
    The code you had at first may be the best since you wan't to sort on multiple columns at the same time.

    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Track changes in worksheet change using arrays

    Hello Yasser,

    I don't know in which you tested... I've made slight modification to the code so prevent some error.
    However note that I did some tests and in the cases where you have duplicate value it will not work properly. The Sort2Dvert function doesn't work in those case (sorting 2 columns same time) and I don't have the time to update it.
    The code you had at first may be the best since you wan't to sort on multiple columns at the same time.

    Please Login or Register  to view this content.

  8. #8
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: Track changes in worksheet change using arrays

    Thank you very much Mr. GC Excel
    It is better to upload a sample to work with it ..Have a look at this sample
    I have put the expected output
    Attached Files Attached Files
    Last edited by YasserKhalil; 10-04-2016 at 08:13 PM.

  9. #9
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Track changes in worksheet change using arrays

    I don't believe it's possible to do without using a helper range.
    Reason is, you want to keep the previous value from column C and D when you input new value in D.
    Macro will be triggered AFTER you have entered the new value in D.
    Without a helper range to store previous value, you will only preserve the new value.

    Might be possible if you input new value in E, then macro would add new combination (C+E), sort and output with one extra row in C+D only.

  10. #10
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: Track changes in worksheet change using arrays

    Thanks a lot for reply
    I imagine a scenario for this .. at workbook open to declare an array which will store the range values .. then if changes happened the array is redimed preserved and sorted then to be put in the range and store the new values in the array again ..
    I think it is possible but I can't figure it out yet
    Thanks a lot for sharing my issue

+ 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. track change
    By Marisa in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-12-2014, 11:24 AM
  2. change log to track data
    By bqheng in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-29-2014, 08:00 PM
  3. Track column value change
    By hoss88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-27-2013, 08:03 AM
  4. Track Changes - can I change this?
    By AnneFr in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 11-02-2012, 04:37 AM
  5. [SOLVED] Change # of Days in Track Changes.
    By KarenABS in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 02-20-2006, 08:30 AM
  6. How to export/save the track change history into another worksheet?
    By jeer in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-11-2006, 05:32 PM
  7. Change Event Track
    By Rafael in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-10-2005, 01:06 PM

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