Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

REVISED Inserting/Copy/Paste A Row > 2 Conditions, Loop VBA

0

Hello To All, whomever all is.  I hope you enjoy challenges.  I have the following data I am working on that I need help with.  I have two VBA codes for inserting rows which exists in this file but am I struggling with the remaining code associated with copy/paste.  For obvious reasons the employee data is fictitious.

The data reflects when an employee changed their time card to different codes (account, sub act, project, or task) data, or in other cases where you see from/to codes that are the same it indicates the only change is either decreasing/increasing hours worked, or where you see there is no CC From or CC To.  These transactions is how which the data appears.  Keep in mind I am working with 20,000+ rows of data all sorted by ID, Date, Project Code to get it into the From/To format and as you can tell there isn't a one to one ratio in distribution of hours which I am not concerned about. Column L, "False", indicates the next employee who changed their time card and where From/To format does not exist. The formula is listed above I.  Line 14/15 is how the data should be per employee per date. I highlighted in yellow how codes seem to be the same but are slightly different.

END STATE:  To get the data into a From/To Format consecutively by Employee Identification (E ID), on that EXP_ITEM_DATE (Date), not  CC From CC From or CC To CC To consecutively.

The bottom line is that for every consecutive CC From or CC To I need to insert a row and copy the data from whichever row remaining that has the missing data, which would be a CC From or CC To, by Employee Identification (E ID), on that EXP_ITEM_DATE (Date), changing the copied rows to a different color of font so that they are easy to locate). If there is isn't a CC From or CC To offset then the existing data for those rows then the data that exists needs to be copied/inserted with the copied cells column "C" FR/TO, changed to whatever is missing and in column "K" HOURS, changed to zero (0). 

 Examples:  As shown in Figure 2.

 Look at employee Flinstone's transactions dated 4/10 and 4/11

4/10 – He is missing 2 CC From(s) in order to offset the remaining CC To(s).  You would insert a row after 3 and 4 then you would copy the data from row 2 since it is the only CC From and paste the data in the newly inserted rows so that there is a From/To format.

4/11 – He is missing 1 CC To(s) in order to offset the remaining CC From(s).  You would insert a row after 6 and then you would copy the data from row 8 since it is the only CC To and paste the data in the newly inserted row so that there is a From/To format.

Look at employee Dino's transactions dated 6/12 through 6/15

6/12 – 6/14 –  There is no CC From(s) transactions in order to offset the CC To(s).  You would copy the existing CC To(s), change column "C" CC To(s) to CC From(s), and then change column "K" HOURS to zero (0), then resort. [Doing the same for no CC To(s) – Bamm Bamm]

6/15 – He is missing 1 CC From(s) in order to offset the remaining CC To(s).  You would insert a row after 26 and then you would copy the data from row 25 since it is the only CC From and paste the data in the newly inserted row so that there is a From/To format.

The attached file has two tabs. One instruction and the other Sample Data.

Figure 1 - The original time card.
The bold transactions that are in the correct format.

Step 1 (Figure 2) – Insert row

This is what it should look like when I insert rows.  I have two macros listed in this file you can review and look at.  The figure indicates the row that needs to be copied which is based on what is missing in order to get it into the CC From/CC To, format.  In this case I need to insert a row after 3, 4, 6, 10, 11, 12, 16, 18, 19, etc……. so that I can copy/paste with either a CC From or a CC To, based on what transaction is missing for that Employee Identification (E ID) , on that EXP_ITEM_DATE (Date). See examples provided at the beginning of this email.

Look at employee Flinstone's transactions dated 4/10 and 4/11

4/10 – He is missing 2 CC From(s) in order to offset the remaining CC To(s).  You would insert a row after 3 and 4 then you would copy the data from row 2 since it is the only CC From and paste the data in the newly inserted rows so that there is a From/To format.

4/11 – He is missing 1 CC To(s) in order to offset the remaining CC From(s).  You would insert a row after 6 and then you would copy the data from row 8 since it is the only CC To and paste the data in the newly inserted row so that there is a From/To format.

Look at employee Dino's transactions dated 6/12 through 6/15

6/12 – 6/14 –  There is no CC From(s) transactions in order to offset the CC To(s).  You would copy the existing CC To(s), change column "C" CC To(s) to CC From(s), and then change column "K" HOURS to zero (0), then resort. [Doing the same for no CC To(s) – Bamm Bamm]

6/15 – He is missing 1 CC From(s) in order to offset the remaining CC To(s).  You would insert a row after 26 and then you would copy the data from row 25 since it is the only CC From and paste the data in the newly inserted row so that there is a From/To format.

Step 2 (Figure 3)  – Copy/Paste Data To The Inserted Rows

The rules for copying is based on what is missing in terms of CC From or CC To for that Employee Identification (E ID) , on that EXP_ITEM_DATE (Date), changing the copied rows to a different color of font so that they are easy to locate (blue bold).  For the transactions not having a CC From or CC To then copy the existing data for those rows so that they can offset, changing copied/inserted cells column "C" CC FR/TO, to whatever is missing, and then in column "K" HOURS, changed to zero (0), then resort to get it into the FR/TO format for that date, then updating the True/False column which should then reflect only where a new employee begins on the spreadsheet.

Answer
Discuss

Answers

0
Selected Answer

Hello Cora,

The plot thickens! Your better explanation doesn't improve clarity, especially with a revised workbook not being attached. I intend to split your question into several topics and hope you will play along. In this thread I should like to solve the problem of how to define employees which are "same" within the context of your data. For whatever follows please ask another question which can start from the point we shall have reached after this one.

Your list identifies employees by their E ID. However, employees may have several accounts and sub-accounts, and they may be associated with different projects and tasks, commenced and ending on different dates. Your data is sorted by all of these criteria. The job is to define the start and end row of a range pertaining to the same employee and considering the small differences that might make you want parts of that range grouped separately.

The way I suggest we tackle the problem is to simply create a list of which of the data must be the same in order to qualify for conclusion in the same list. Here are the criteria.

  1. E ID must be same
  2. Account No must be same
  3. Sub-Account must be same
  4. Project No must be same
  5. Task No. must be same
  6. Exp_Item Date must be same
  7. Exp_End Date must be same

Please correct the list (or approve it). That will unequivocally clarify what you want. Meanwhile you already did address what looks to me like the same question. You used this formula in column J.

=IF(B4=B5,AND(D4<>D5))

Now, the IF() function consists of 3 parts as follows.
IF([Expression], [Result if True], [Result if False])
Applied to your above formula, B4=B5 is the Expression. AND(D4<>D5) is the Result if True. There is no Result if False. Actually, Excel will presume that the missing instruction evaluates to False. In the given context that may produce the desired result.

The AND() function has the following syntax.
AND([Expression1], [Expression2], more expressions)
In your formula there is only a single expression. Therefore the result of AND(D4<>D5) is exactly the same as D4<>D5. I suspect that what you had in mind is this:

=AND(B4=B5, D4<>D5)

However, this formula is a very far way from my above list if it is to determine where the next employee's data start. Observe that column D (From/To) doesn't help at all in this respect and is omitted from my above list for that reason. Moreover, instead of looking at the 6 criteria that might be relevant it concentrates on only the E ID. In order to expand the formula to include all six criteria of potential significance the formula might look like this:

=AND(B4=B5, E4=E5, F4=F5, G4=G5, H4=H5, I4=I5)

Of course, if you would correct this formula to exclude the items that don't have to be identical.

Edit 26 Sep 2018  ==================================

Based on your clarification in the discussion below, only E ID and EXP_ITM_Date are significant. Therefore the correct formula in column J should be

=AND(A4=A5, I4=I5)

Please test and confirm. Note that testing for CC FROM and CC TO in this formula is not required because missing rows are inserted by the code which doesn't use column J.

End of Edit 
However the question raised in the next paragraph still remains without an answer.  ===========================================

And that fact leads me to the last point I would like to address in this thread: Why should you need this identical calculation in both the worksheet and the code? If TRUE or FALSE is needed in column J for some task we aren't discussing here the code could write the result to the sheet instead of the formula.

On the other hand, if you wish column J to be responsive to possible future changes in columns E:I it would be desirable that the formula is correct and flexible enough to deal with whatever will be demanded of it.

Discuss

Discussion

Hi! I love your intro statement. For whatever reason it appears I am unable to attach the data, copy paste data to make it look presentable, or paste images of the data. In any case I will do my best to address what appears to be chaotic.  First so you can see what I am talking about I ask if you can kindly put the following information in an excel spreadhseet so you can see what it is I am asking for help with.
Column A (Just the number of rows) for this example put 2 through 8. Column B (EE ID) for this example put 1234 (copy downn to row 8)
Column C (Name) for this example put Flinstone (do same as B)
Column D (FR/TO) for this example put CC From, followed by 3 CC To, followed by 2 CC From, and followed by 1 CC To
Column E - H are accounting strings which I am not concerned with so you can put the abc123 in those columns.
Column I (Date of Time Card) starting with row 2 put 4/10/2018 (4 Times), followed by 4/11/2018 (3 Times)
Column J (Date when timecard was fixed put 4/15/18 not important)
Column K (Hours) starting with row 2 put, (8),2,4,2,(2),(6),8
Column L (True/False) starting with row 2 put T,F,F,T,F,T,F
Looking at this example what are we missing to get these transactions in CC From/CC To format by date for this employee?
4/10 - I need 2 CC From(s) to offset the CC To(s). So, since there is only one CC From listed I will copy that row to offset the CC To(s) listed.
4/11 - I need 1 CC To to offset the CC From(s). Since there is only one CC To listed I will copy that row to offset the CC From(s) listed.
The data goes into the inserted lines with the code I have.
CoraG (rep: 6) Sep 21, '18 at 11:52 am
I have the exact same code for another Sub Insert Row for the CC To Column. They are executed separately.  The True/False Column reflects two things. It lets me know when the next different employee is listed on the spreadsheet and if there are consecutive CC From/CC To.  Let me know after you have inserted the small sample into a spreadsheet if it makes sence. Thank you

Option Explicit
Dim Lastrow As Long
Dim i As Long
Sub InsertRow3()     Application.ScreenUpdating = False   
    Lastrow = Worksheets("SAMPLED DATA").Cells(Rows.Count, 1).End(xlUp).Row
    For i = (Lastrow - 1) To 2 Step -1
     If Worksheets("SAMPLED DATA").Cells(i, "D").Value = "CC From" And _
        Worksheets("SAMPLED DATA").Cells(i + 1, "D") = "CC From" Then        
          Worksheets("SAMPLED DATA").Rows(i + 1).Insert Shift:=xlUp
        End If                  
   Next i
        Application.ScreenUpdating = True   
End Sub
CoraG (rep: 6) Sep 21, '18 at 12:38 pm
Hello Cora,
We are talking past each other now. I have a copy of the workbook you originally posted. For the purpose of this thread I don't need another one. Your code for inserting rows doesn't work correctly as I have pointed out in the discussion under your original post which you deleted. Here is my plan:
First step insert blank rows. Second step fill the blanks. Your existing code produces errors because it can't determine where one employee ends and the next one starts. Blanks must be filled with data from the same employee. The common problem is to define start and end of each employee data. Therefore I suggested to solve this problem - and only this problem - in this thread. I explained what's wrong with your formula and asked for precise details of your rules. You graciously accepted my answer (thank you) but didn't respond to my request for information. Without your response the problem will not be solved because you don't see the problem and I need your input.
Meanwhile, I request once again that you split your project into a Q & A format which might benefit other visitors to this forum. Nobody wants to read our long correspondence. Please don't push this thread to deal with inserting of rows. We should define first and last rows of one employee's data here. Not less and not more.
Variatus (rep: 4889) Sep 21, '18 at 11:13 pm
The difference between a CC From and CC To row must be in a column you aren't including in your question, most likely a date or a time. If the only difference between the From row and To row is a date/time then the two data should be in different columns in the same row. Having different rows results from automatically created data. Why must the manually repaired data have the format of the automatically created? Instead of inserting rows you might consider re-writing the data.
(a) Insert a column for From and another one for To.
(b) Transfer the available dates from the existing column to the appropriate new one, leaving the other one blank and marked for attention.
(c) Delete FR/TO column Bam Bam (as you would say), save 10,000 rows. Of course, you will still need to be able to tell where each employee's data starts and ends. That is the question I try to answer in this thread. After you have the answer to that you will be able to formulate your next question. Perhaps you will ask to insert the missing rows or re-write the data. Either way, progress will only be possible if this thread is carried to a successful end. Success is when we can define first and last row of each employee's data.
Variatus (rep: 4889) Sep 21, '18 at 11:46 pm
Hello - Thank you for your response.  So, initially the insert row code was pointed to the True/False column which provides two things.  First where the next employee starts and secondly where there are consecutive CC From(s) and CC To(s). Then I changed it because I figured that inserting the rows needed to be based on where ever the consecutives CC From(s)/CC To(s) existed regardless of where a new employee starts and ends.  The start and end should be in the code for pasting.  So, for this purpose I can change the True/False column to only tell me when the next employee starts which again should be used for the pasting process only. 

Inserting Rows - Rules
1. E ID must be the same
2. Exp_Item Date must be the same
3. CC From/CC To Column - Consecutive

If you want me to outline the qualifiers for the paste in a different thread I most certainly can, otherwise here are the rules for the pasting.
Gracias, Cora
CoraG (rep: 6) Sep 25, '18 at 12:05 pm
Pasting - Rules
1. E ID must be the same
2. Exp_Item Date must be the same
3. True/False Column - False being the indicator of the next employee
4. CC From/CC To Column - What are we missing and what do we have for this employee on this date.  If only one CC From then copy that paste to the inserted lines. If only one CC To then copy that and paste to the inserted lines for that employee for that date. If no CC From exists to offset the CC To then Copy the CC To and change the text to CC From, change HOURs Column to 0(zero), then resort those transactions for that date on Project Number.

Let me know.  The rest of these parameters you listed are not important.

1. Account No must be sam
2. Sub-Account must be same
3. Project No must be sam
4. Task No. must be same
5. Exp_End Date must be same

Let me know. Gracias, Cora
CoraG (rep: 6) Sep 25, '18 at 12:52 pm
Good morning Cora,
Thank you for clarifying. Now let's wrap up this thread. Remember that this thread is about the formula in column J (only!). Please refer to the edit I inserted in my answer above and ask if you don't understand my suggestion because nothing more can be done until this point is both correctly resolved and fully understood. The next step is code to insert blank rows. Bear in mind that the code you have does insert rows but not all required rows. Please start another thread outlining the task. The task is to insert missing From or To rows based on (1) the rule that To must follow From, (2) From must precede To AND (3) Next Employee is identified by the formula in column J. This is all exactly as you had it originally, except that your formula in column J was wrong. Attach a copy of your trial data with the corrected formula to your question. Please don't ask for any Copy/Paste in the new thread. That is a separate operation which can't be started until after blank rows have been inserted correctly.
Variatus (rep: 4889) Sep 25, '18 at 9:21 pm
Strictly speaking, column J isn't required for inserting blanks because the rules which it illustrates will be written into VBA code. There will be no reference to column J in the code, not for inserting rows and not for copy/paste (third question), either. Therefore it will eventually become important to understand your intentions for this column. Since the issue of column J and its formula is the content of this thread the issue should better be resolved here and not carried into the future.
Variatus (rep: 4889) Sep 25, '18 at 9:22 pm
I point out that your new definition of "next employee" is at variance with your original instruction where you said that 7445 (Scott) in row 14 was different from 7445 (Scott) in row 13 because the account number differs. You highlighted this yellow in your trial data and especially mentioned it in your instructions.
Now you state equally unequivocally that the account number is irrelevant. Only one of your statements can be correct. Which one?
Variatus (rep: 4889) Sep 25, '18 at 9:41 pm
Okay, I believe I should start this thread entirely all over separting the events as this entire projects has confused you and I wasn't sure what spreadsheet you received since it didn't seem to have worked when I initially started this thread. So, lets forget about this project and I will separate the what needs to happen.  I will call the first one Insert Row Based on Consecutive Qualifiers and the next one I will call it Copy/Paste Row Data Based On Several Qualifiers.  I will try to upload the spreadsheet sample with a tab of instructions.  The only reason for me highlighting about the codes is to let you know that there is slight differences only not that it is important as it isn't. I will also put the code that I have in the VBA tab in the sample I will provide. Please look out for them. Thanks for your help but now I believe you may be getting me confused.. ha! so lets start this different.
CoraG (rep: 6) Sep 27, '18 at 4:58 pm
I'll follow you to your next step but ask you to bear in mind not only what you want but also what you most certainly don't want. You don't want 100 errors or omissions in 20,000 entries. If you can't do it 100% correct, don't even try. Good luck!
Variatus (rep: 4889) Sep 27, '18 at 8:54 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login