Inserting a row inbetween consecutive conditions - Need Help VBA

CoraG · Aug 30, 2018

I am having difficulty a code that provides for inserting a row when there are to FALSE that fall consecutively. I would like to insert a row when this shows up. Do I use AND IF statement? The True/False column is M on my spreadsheet.

Lastrow = Worksheets("APR ADJ").Cells(Rows.Count, 12).End(xlUp).Row
For i = Lastrow To 2 Step by - 1

If Worksheets("APR ADJ").Cells(i, 12).Value = "FALSE FALSE" Then
ActiveCell.EntireRow.Insert Shift:=xlDown
End If
Next
Worksheets("APR ADJ").Cells(1, 1).Select

CoraG · Sep 4, 2018

As I previously stated I apologize that this seems to be changing, those were truly not my intentions so I did not mean to cause you frustration. Being prior military I do my best to thoroughly research things before absolutely requesting assistance and I was cut off guard by the timing of this request. I was hoping to be able to upload the spreadsheet to help you better understand but it isn't to do that here. Given your understanding of the project I appreciate your recommendation on how this should best be approached.
Step 2 – Copy the data to the inserted rows
Copy Line 2 Data – To the blank row after 3, after, 4 (This is copying From data)
Copy Line 8 Data – To the blank row after 6 (This is copying To data)
Copy Line 9 Data – To the bank row after10, after 11, after 12 (This is copying From data)
Copy Line 18 Data – To the blank row after 16 (This is copying To data)

1	E ID	EE	FR/TO	Account	Sub Act	PROJECT_NUMBER	TASK_NUMBER	EXP_ITEM_DATE	EXP_END_DATE
2	4879	Hudson	CC From	MB0502000	5Y110B	KK0100	30060000	4/10/2018	4/15/2018
3	4879	Hudson	CC To	MB0502000	5Y110B	KKEQ00	30061000	4/10/2018	4/15/2018
COPY LINE 2 DATA
4	4879	Hudson	CC To	MB0502000	5Y110B	KKEQ00	30111000	4/10/2018	4/15/2018
COPY LINE 2 DATA
5	4879	Hudson	CC To	YN0100000	5Y110B	X5D100	0010AX33	4/10/2018	4/15/2018
6	4879	Hudson	CC From	YN0100000	3B100A	XA3100	0011B5L3	4/11/2018	4/15/2018
COPY LINE 8 DATA
7	4879	Hudson	CC From	YN0100000	3B100A	XA3100	0011BHRL	4/11/2018	4/15/2018
8	4879	Hudson	CC To	YN0100000	3B100A	XA3100	0011B5L3	4/11/2018	4/15/2018
9	7445	Scott	CC From	YN0100000	4R500A	SWDP00	70WPM6RE	4/5/2018	4/8/2018
10	7445	Scott	CC To	YN0100000	4R500A	SWDP00	70WQMA00	4/5/2018	4/8/2018
COPY LINE 9 DATA
11	7445	Scott	CC To	YN0100000	4R500A	X7CE00	PUTMANAL	4/5/2018	4/8/2018
COPY LINE 9 DATA
12	7445	Scott	CC To	YN0100000	4R500A	XE4500	ELECTEAM	4/5/2018	4/8/2018
COPY LINE 9 DATA
13	7445	Scott	CC To	YN0100000	4R500A	XUEP00	7JE80213	4/5/2018	4/8/2018
14	7445	Scott	CC From	YN0100000	4R500A	XE4500	ELECTEAM	4/6/2018	4/8/2018
15	7445	Scott	CC To	YN0100000	4R500A	XE4525	ELECTEBN	4/6/2018	4/8/2018
16	8335	Allen	CC From	GD6030300	5J880A	HG5500	00000000	2/15/2018	2/18/2018
COPY LINE 18 DATA
17	8335	Allen	CC From	GD6030300	5J880A	HG5600	00000000	2/15/2018	2/18/2018
18	8335	Allen	CC To	450140300	5J880A	RTBL00	00000000	2/15/2018	2/18/2018

CoraG · Sep 4, 2018

This is what the final data should look like.

1	E ID	EE	FR/TO	Account	Sub Act	PROJECT_NUMBER	TASK_NUMBER	EXP_ITEM_DATE	EXP_END_DATE
2	4879	Hudson	CC From	MB0502000	5Y110B	KK0100	30060000	4/10/2018	4/15/2018
3	4879	Hudson	CC To	MB0502000	5Y110B	KKEQ00	30061000	4/10/2018	4/15/2018
4→	4879	Hudson	CC From	MB0502000	5Y110B	KK0100	30060000	4/10/2018	4/15/2018
5	4879	Hudson	CC To	MB0502000	5Y110B	KKEQ00	30111000	4/10/2018	4/15/2018
6→	4879	Hudson	CC From	MB0502000	5Y110B	KK0100	30060000	4/10/2018	4/15/2018
7	4879	Hudson	CC To	YN0100000	5Y110B	X5D100	0010AX33	4/10/2018	4/15/2018
8	4879	Hudson	CC From	YN0100000	3B100A	XA3100	0011B5L3	4/11/2018	4/15/2018
9→	4879	Hudson	CC To	YN0100000	3B100A	XA3100	0011B5L3	4/11/2018	4/15/2018
10	4879	Hudson	CC From	YN0100000	3B100A	XA3100	0011BHRL	4/11/2018	4/15/2018
11	4879	Hudson	CC To	YN0100000	3B100A	XA3100	0011B5L3	4/11/2018	4/15/2018
12	7445	Scott	CC From	YN0100000	4R500A	SWDP00	70WPM6RE	4/5/2018	4/8/2018
13	7445	Scott	CC To	YN0100000	4R500A	SWDP00	70WQMA00	4/5/2018	4/8/2018
14→	7445	Scott	CC From	YN0100000	4R500A	SWDP00	70WPM6RE	4/5/2018	4/8/2018
15	7445	Scott	CC To	YN0100000	4R500A	X7CE00	PUTMANAL	4/5/2018	4/8/2018
16→	7445	Scott	CC From	YN0100000	4R500A	SWDP00	70WPM6RE	4/5/2018	4/8/2018
17	7445	Scott	CC To	YN0100000	4R500A	XE4500	ELECTEAM	4/5/2018	4/8/2018
18→	7445	Scott	CC From	YN0100000	4R500A	SWDP00	70WPM6RE	4/5/2018	4/8/2018
19	7445	Scott	CC To	YN0100000	4R500A	XUEP00	7JE80213	4/5/2018	4/8/2018
20	7445	Scott	CC From	YN0100000	4R500A	XE4500	ELECTEAM	4/6/2018	4/8/2018
21	7445	Scott	CC To	YN0100000	4R500A	XE4525	ELECTEBN	4/6/2018	4/8/2018
22	8335	Allen	CC From	GD6030300	5J880A	HG5500	00000000	2/15/2018	2/18/2018
23→	8335	Allen	CC To	450140300	5J880A	RTBL00	00000000	2/15/2018	2/18/2018
24	8335	Allen	CC From	GD6030300	5J880A	HG5600	00000000	2/15/2018	2/18/2018
25	8335	Allen	CC To	450140300	5J880A	RTBL00	00000000	2/15/2018	2/18/2018

Joe4 · Sep 4, 2018

OK, I think I see the pattern of where to insert the blank rows - it looks like that given any row, if the following row has the same value in column C, insert the blank row between that row and the following row.
Do I have that correct?

However, I fail to understand the pattern/rules on this part:

Step 2 – Copy the data to the inserted rows
Copy Line 2 Data – To the blank row after 3, after, 4 (This is copying From data)
Copy Line 8 Data – To the blank row after 6 (This is copying To data)
Copy Line 9 Data – To the bank row after10, after 11, after 12 (This is copying From data)
Copy Line 18 Data – To the blank row after 16 (This is copying To data)

As a prior military person, let me appeal to you this way - what are the RULES/PROCEDURES for determining where exactly each inserted row should be copying information from?
Can you define some concrete rules as to how that is determined?

CoraG · Sep 17, 2018

Hello - As per your words, “it looks like that given any row, if the following row has the same value in column C, insert the blank row between that row and the following row. Do I have that correct?”
CORRECT, based on DATE and EE, this is the consecutive issue. From From or To To…
COPYING DATA – Look at Hudson, E ID 4879, for his transactions dated (EXP_ITEM_DATE Column) 4/10/18 and 4/11/18 and E ID 8335, Allen, 2/15/18.
As I stated this is about the distribution of time being charged to the wrong codes and employees fixing their time to charge to the correct codes. In Example 1, he charged (CC From) KK0100 incorrectly when he should have charged the other codes. I need to reflect where the distribution of time came from to the other projects which is why I need to insert the row. All the hours he reported on the CC From Line, which is his original time card now need to be distributed to the CC To Line. The Hours column is not reflected in any of the samples I provided since I am more concerned with the Project Number and Task Account distributions and I didn’t want to add to the confusion.
Example 1 – Original Time Card

From/To Column Project Number Task Hours
CC From KK0100 30060000 - 8
CC To KKEQ00 30061000 + 2
CC To KKEQ00 30111000 + 4
CC To X5D100 0010AX33 + 2

The bold figures reflects the inserted line and the changes I need for that DATE, for that EMPLOYEE.
From/To Column Project Number Task Hours
CC From KK0100 30060000 - 2
CC To KKEQ00 30061000 + 2
CC From KK0100 30060000 - 4
CC To KKEQ00 30111000 + 4
CC From KK0100 30060000 - 2
CC To X5D100 0010AX33 + 2

Example 2 – Original Time Card
From/To Column Project Number Task Hours
CC From XA3100 0011B5L3 - 2
CC From XA3100 0011BHRL - 6
CC To XA3100 0011B5L3 + 8

The bold figures reflects the inserted line and the changes I need for that DATE, for that EMPLOYEE.
From/To Column Project Number Task Hours
CC From XA3100 0011B5L3 - 2
CC To XA3100 0011B5L3 + 2
CC From XA3100 0011BHRL - 6
CC To XA3100 0011B5L3 + 6

Example 3 – Original Time Card (Employee ID 8335, Allen, 2/15/18)
From/To Column Project Number Task Hours
CC From HG5500 00000000 - 5
CC From HG5600 00000000 - 3
CC To RTBL00 00000000 + 8

The bold figures reflects the inserted line and the changes I need for that DATE, for that EMPLOYEE.
From/To Column Project Number Task Hours
CC From HG5500 00000000 - 5
CC To RTBL00 00000000 + 5
CC From HG5600 00000000 - 3
CC To RTBL00 00000000 + 3

So I hope this clarifies any questions you have and identifies rules/procedures by which the information should be copied from. The rules for copying is based on Employee, Date, and what we are lacking per date. In Example 2 and 3 we are missing a CC To. So, then you copy the CC To information that currently exists for that Employee for that Date. Example 1 we are missing many CC From(s). So, then you copy the CC From that currently exists for that Employee that Date.

Given that I left this matter alone for a while so as to not add any further frustration or confusion I hope I have defined what it is you are asking of me.

Joe4 · Sep 18, 2018

I am sorry. Maybe it has been because we have been away too long from it, but it is not really making any sense to me. I am afraid I am going to have to bow out.

Due to the complexity of your issue, you may be pushing the limits of what can be expected of free help, and might want to look at Consulting Services.
There are some listed here: https://www.mrexcel.com/consulting-services/

CoraG · Sep 19, 2018

Hi Joe - Thank you so very much for giving it a try, no biggie. I understand and I appreciate your help. Sincerely, Cora

Joe4 · Sep 19, 2018

You are welcome.

Good luck, I hope you get it working the way you want.

Inserting a row inbetween consecutive conditions - Need Help VBA

CoraG

New Member

CoraG

New Member

Excel Facts

CoraG

New Member

Joe4

MrExcel MVP, Junior Admin

CoraG

New Member

Joe4

MrExcel MVP, Junior Admin

CoraG

New Member

Joe4

MrExcel MVP, Junior Admin

Similar threads

Forum statistics

Share this page

Inserting a row inbetween consecutive conditions - Need Help VBA

New Member

New Member

Excel Facts

New Member

MrExcel MVP, Junior Admin

New Member

MrExcel MVP, Junior Admin

New Member

MrExcel MVP, Junior Admin

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock