Inserting a row inbetween consecutive conditions - Need Help VBA

[CoraG]

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

 

[Joe4]

Welcome to the Board!

Don't use ActiveCell, as that is the cell that is active when your code starts, and you are selecting any cells in your code.
Try something like this:

[COLOR=#333333]Lastrow = Worksheets("APR ADJ").Cells(Rows.Count, 12).End(xlUp).Row[/COLOR]
[COLOR=#333333]For i = Lastrow To 2 Step by - 1[/COLOR]
[COLOR=#333333]    If Worksheets("APR ADJ").Cells(i, 12).Value = "FALSE FALSE" Then[/COLOR]
[COLOR=#333333]        Rows(i).Insert Shift:=xlDown[/COLOR]
[COLOR=#333333]    End If[/COLOR]
[COLOR=#333333]Next[/COLOR]
[COLOR=#333333]Worksheets("APR ADJ").Cells(1, 1).Select[/COLOR]

If you want it to insert the row below the row instead of above it, change the Insert line to:

[COLOR=#333333]       Rows(i+1).Insert Shift:=xlDown[/COLOR]

 

[CoraG]

It didn't do anything... not sure why.... This part of the data set..

1

2

3

BNR

ORG_NAME

PROJECT_NUMBER

TASK_NUMBER

EXP_ITEM_DATE

EXP_END_DATE

HOURS

SLR_RATE

LABOR_COST

1,2,3

2456

Robert

CC From

NN9100000

3S000A

H9BA00

FORM0100

2/6/2018

2/11/2018

-9

96.14

$ (865.26)

TRUE

2456

Robert

CC To

NN9100000

3S000A

H9BA00

FORM0100

2/6/2018

2/11/2018

2

96.14

$ 192.28

FALSE

2456

Robert

CC To

MB0104021

3S000A

JW8S00

QUALPLCH

2/6/2018

2/11/2018

7

96.14

$ 672.98

FALSE

<colgroup><col span="8"><col><col span="2"><col><col></colgroup><tbody>
</tbody>

See how the there are consecutive False... I need to insert a row between the two False transactions which are on column M

So every time there are False transactions one after the other is where I need the row inserted. I hope this helps

 

[Joe4]

I thought your code looked a little funny.

A few problems with your code:
- Column "M" is column 13, not column 12 (note: in Cells(row,column), you can use the column number or letter)
- You want to check for FALSE on two separate rows. Your code currently looks for two FALSE entries in the same cell
- It should be "Step -1", not "Step by -1"
- FALSE and "FALSE" are not the same thing. One is a boolean value (FALSE) and one is literal text ("FALSE"). I am not sure which one you have.

Here is updated code that should do what you want. If it does not do anything, try changing FALSE to "FALSE" in the code.

Sub MyInsertRows()

    Dim Lastrow As Long
    Dim i As Long
    
    Application.ScreenUpdating = False
    
    Lastrow = Worksheets("APR ADJ").Cells(Rows.Count, 12).End(xlUp).Row
    For i = (Lastrow - 1) To 2 Step -1
        If Worksheets("APR ADJ").Cells(i, "M") = False And _
            Worksheets("APR ADJ").Cells(i + 1, "M") = False Then
            Worksheets("APR ADJ").Rows(i + 1).Insert Shift:=xlDown
        End If
    Next i

    Application.ScreenUpdating = True
    
End Sub

 

[CoraG]

YOU ROCK!!!!

This really worked... So I caught the error as in the column 12. I am assuming my FALSE are boolean value since I didn't do a copy paste special value to change it to text. The other question I have is if you notice in column 3 CC From, CC To, CC To. The reason I needed to insert a row is to add the column to offset the last CC To. What I need to do is copy the From Data line up to the 9th column. How do you recommend I do this. Boy have you made life simpler for me... I am talk about thousands of rows of data I am having to do this with. Let me know thoughts. Thank you!

 

[Joe4]

What I need to do is copy the From Data line up to the 9th column.

OK, so in the simple example you posted, we are inserting a blank line under row 3 to separate the two FALSE rows (when looking at column "M").
It sounds like you want to copy column A-I to this new blank row, but I am unclear if we are copying from the row above the inserted row we just created, or from the just below the insert row.
Can you clarify that?

And do we really need column C to say "CC From", not "CC To" (which is what we would get if we were copying from the adjacent row)?

 

[CoraG]

Okay, so let me clarify. The data needs to be in From To format for every row. If you look at the first data you helped me create a row so I can copy the data from the CC From. What I need is to be able to copy the data up to EXP_END_DATE (Column 9) like so....

(From/To/To) -

FIRST DATA SET

1

2

3

BNR

ORG_NAME

PROJECT_NUMBER

TASK_NUMBER

EXP_ITEM_DATE

EXP_END_DATE

HOURS

SLR_RATE

LABOR_COST

1,2,3

2456

Robert

CC From

NN9100000

3S000A

H9BA00

FORM0100

2/6/2018

2/11/2018

-9

96.14

($865.26)

TRUE

2456

Robert

CC To

NN9100000

3S000A

H9BA00

FORM0100

2/6/2018

2/11/2018

2

96.14

$192.28

FALSE

2456

Robert

CC From

NN9100000

3S000A

H9BA00

FORM0100

2/6/2018

2/11/2018

2456

Robert

CC To

MB0104021

3S000A

JW8S00

QUALPLCH

2/6/2018

2/11/2018

7

96.14

$672.98

FALSE

<tbody>
</tbody>

The data set may also look like this
(From/From/To) - Which in this case I would need to copy the CC To column. The first code helped me separate all the consecutive FALSE transaction where either of these type of situations occur in the data set.

SECOND DATA SET

1

2

3

BNR

ORG_NAME

PROJECT_NUMBER

TASK_NUMBER

EXP_ITEM_DATE

EXP_END_DATE

HOURS

SLR_RATE

LABOR_COST

1,2,3

3256

Sam

CC From

NN9100000

3S000A

H9BA00

FORM0100

2/6/2018

2/11/2018

-9

96.14

($865.26)

FALSE

3256

Sam

CC From

NN9100000

3S000A

H9BA00

FORM0100

2/6/2018

2/11/2018

-2

96.14

$192.28

FALSE

2456

Sam

CC To

MB0104021

3S000A

JW8S00

QUALPLCH

2/6/2018

2/11/2018

7

96.14

$672.98

TRUE

<colgroup><col span="5"><col><col><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

So, the end state is to copy the CC From where needed or the CC To where needed up to Column 9. I hope this makes sense now.

 

[CoraG]

Correction - "Which in this case I would need to copy the CC To row up to column 9." Sorry about that

 

[Joe4]

I am not sure that you answered the question I was after, and that is which row is it copying from.
But in your first example, it looks like the blank row that is being inserted is copying from the row just above it. So I am going to proceed under that assumption.

Sub MyInsertRows()

    Dim Lastrow As Long
    Dim i As Long
    
    Application.ScreenUpdating = False
    
    Lastrow = Worksheets("APR ADJ").Cells(Rows.Count, 12).End(xlUp).Row
    For i = (Lastrow - 1) To 2 Step -1
        If Worksheets("APR ADJ").Cells(i, "M") = False And _
            Worksheets("APR ADJ").Cells(i + 1, "M") = False Then
            Worksheets("APR ADJ").Rows(i + 1).Insert Shift:=xlDown
'           Copy columns 1-9 from row above
            Worksheets("APR ADJ").Range(Cells(i, "A"), Cells(i, "I")).Copy _
                Worksheets("APR ADJ").Cells(i + 1, "A")
'           Update value in column C
            If Worksheets("APR ADJ").Cells(i + 1, "C") = "CC From" Then
                Worksheets("APR ADJ").Cells(i + 1, "C") = "CC To"
            Else
                Worksheets("APR ADJ").Cells(i + 1, "C") = "CC From"
            End If
        End If
    Next i

    Application.ScreenUpdating = True
    
End Sub

 

[CoraG]

Hi thank you. So, the reason I didn't give you a row is because there can be one CC From and 5 CC To or 5 CC From(s) to one or several CC To(s)ice versa so it isn't based on row above or below. Is there a way to identify the row to copy from based on that? So, column m identifies all those transactions that are consecutive CC From(s) or CC To(s). Based on Column 8 date.