Selected Answer
The attached workbook has code which will insert missing rows. All the code is in the Module TeachExcel. First, familiarise yourself with the layout of the workbook.
- Tabs Sampled Data and Instructions Part 1 are untouched from what you published. Neither sheet is referenced by the code.
- Tab Modified Sample is a copy of Sampled Data in which the yellow highlight was removed. The purpose of this sheet is to have an "original" of the sheet on which the code is tested.
- Tab Solution is used for testing. The code will modify this sheet. To repeat the test copy Modified Sample to Solution. You should also copy your real data to Solution and run tests.
At the top of the code you will find the enumeration Nws which permits you to define another first data row and change all of the referenced columns. Read the instructions preceding and within the Enum.
The procedure to run is Sub InsertMissignData. You can change the name of the worksheet on which to insert rows in this line of code.
Set Ws = ThisWorkbook.Worksheets("Solution")
You can run the code repeatedly on the same data set without ill effects. You can also modify the (test) data after running the code and run it again. The procedure will call the sub CopyData which is in preparation of the next step and as yet blank. Before we can get there this step of the project must be tested and approved (by you).
For the moment, the function InsertMissingRows does all the work. You may notice that it is unchanged since Sept 28, as is the rest of the project. The progress we made since then is that you have declared column Last Employee irrelevant. That enables ignoring the fact that its formula is wrong. It doesn't prove that my code is correct. This can only be ascertained by you, testing real data. Look for this kind of combination.
- EmployeeA From
- EmployeeB To
The From/To sequence is perfect. Your existing code will not insert a row. In fact, however, two rows are missing. My code will find this omission and insert two rows, provided that it can differentiate between EmployeeA and EmployeeB. You have offered several methods of how to do that and still haven't clarified which one is correct. Therefore my code allows you to modify it to suit your final requirements. Look for the function SameId. Look for these lines of code.
' include the columns of cells which must be identical in the array
' Eligible values:-
' NwsID, NwsAccount, NwsSubAcc, NwsProject, NwsTask, NwsItmDate, NwsEndDate
Clms = Array(NwsID, NwsItmDate)
The function will judge the data in two adjacent rows as belonging to the same employee if the cells in columns NwsID and NwsITMDate are identical. This is controlled by the code line Clms = Array(NwsID, NwsItmDate).
According to one of your earlier explanations, supported by the highlights in your Sampled Data, this isn't enough. You have indicated, and later reversed, that the account number should also be the same. The account number can be included in this function's tests by adding it to the array thus, Clms = Array(NwsID, NwsItmDate, NwsAccount). The sequence of the numbers in the array is immaterial as is the number of elements. I have added a complete list of all eligible columns in the remark above the array's specification, to wit, NwsID, NwsAccount, NwsSubAcc, NwsProject, NwsTask, NwsItmDate, NwsEndDate.
The effect of this function will be even greater when we proceed to copying data. Perhaps the specification for inserting rows and copying data isn't the same. I look forward to seeing your question. For now, we can't proceed to the next step until the inserting is perfect. When you test the code on real data, don't worry too much about the rows which are inserted. Even your previous code - much simpler than mine - could do the From/To sequence almost correctly. One of the tests you should run is to use your old code on the Solutions tab and then run InsertMissingData on the result. The rows inserted by my code are those your earlier code missed. Examine them. Perhaps they should have been missed :-)
Make it your job to find the rows which are not inserted. If the start and end of any employee's data is not defined correctly the result will not be extra rows which are not required. The mistake will be in required rows which were not inserted. That is much harder to find but absolutely necessary if you want to account for employee time correctly.
Once you are sure that the code resulting from this thread is correct, post the workbook I now attach (or its successors as we evaluate your test results) with tabs 1, 3 and 4 deleted, perhaps a worksheet Instructions Part 2 added, code Module1 removed and explain what you need done. Your already existing post for the next step has a different base and doesn't implement what you will have learned from testing the attached code. Mildly put, it isn't so useful.
BTW, the 20K+ rows of your data will take some time to process. Therefore I have added a progress indicator to reassure you that your PC is still working. Look at the status bar at the bottom left of your screen while the code is executing.
