Add a row and fill it with different values based on certain variables

[zack8576]

I need add a row under any row that contains certain keywords, based on these keywords, the new row will be populated with different values.
(note: value in column A and B always copies from the row above)

column E contains "4'dia" and "Invert", column N does not contain "Toho", the new row should look like below:

column E contains "5'dia" and "Invert", column N does not contain "Toho", the new row should look like below:

column E contains "4'dia" and "Invert", column N contains "Toho", the new row should look like below:

The first part is working like a charm, no issues at all, but the "5'dia", and the '4'dia" with "Toho" are not adding a row to the excel file at all, any ideas?
(All the files used for testing do contain the proper keywords) any help is appreciated

code posted below

Public rng2 As Range, lr4 As Long, i As Long
Sub Flowline() 
    Set rng2 = Range("A1").CurrentRegion
    lr4 = rng2.Cells(Rows.Count, "K").End(3).Row

    For i = lr4 To 2 Step -1    
        If rng2.Cells(i, 11) Like "*4'dia**Invert*" And _ 'THIS PART IS WORKING JUST FINE
           Not rng2.Cells(i, 14) Like "*Toho*" Then
            rng2.Cells(i, 11).Offset(1).EntireRow.Insert  
            rng2.Cells(i, 3).Offset(1).Resize(1, 9).Value = _
            Array("1", "F14050J", "Yes", "", "", "185", "Production", "500", "FLOWLINE,4' Diameter")
            rng2.Cells(i, 1).Offset(1).Resize(1, 2).Value = rng2.Cells(i, 1).Resize(1, 2).Value
        If rng2.Cells(i, 11) Like "*4'dia**Invert*" And _ 'THIS PART IS NOT ADDING A ROW
            rng2.Cells(i, 14) Like "*Toho*" Then
                rng2.Cells(i, 11).Offset(1).EntireRow.Insert  
                rng2.Cells(i, 3).Offset(1).Resize(1, 9).Value = _
                Array("1", "F14050XJ", "Yes", "", "", "185", "Production", "500", "FLOWLINE,4' Diameter")
                rng2.Cells(i, 1).Offset(1).Resize(1, 2).Value = rng2.Cells(i, 1).Resize(1, 2).Value
        ElseIf rng2.Cells(i, 11) Like "*5'dia**Invert*" And _ 'THIS PART IS NOT ADDING A ROW
               Not rng2.Cells(i, 14) Like "*Toho*" Then
                rng2.Cells(i, 11).Offset(1).EntireRow.Insert  
                rng2.Cells(i, 3).Offset(1).Resize(1, 9).Value = _
                Array("1", "F15050J", "Yes", "", "", "150", "Production", "500", "FLOWLINE,5' Diameter")
                rng2.Cells(i, 1).Offset(1).Resize(1, 2).Value = rng2.Cells(i, 1).Resize(1, 2).Value 
        End If
      End If
    Next i
End Sub

 

[rlv01]

It may be that you are never satisfying some of your "If" conditions. You could use the VBA debugger to set breakpoints at each row insert statement, then single-step (F8) from that point to observe what happens.

BTW, if you post a graphic image of your data, the number of people willing to manually type in your data to experiment to help you will be limited. Consider using this free tool instead to post some sample data.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Alex Blakenburg]

See if this works for you.
I think it is a simpler structure and easier to follow:

I assume you have the variables set to Public for a reason, so I haven't changed that.

Sub Flowline()
    Set rng2 = Range("A1").CurrentRegion
    lr4 = rng2.Cells(Rows.Count, "K").End(3).Row
       
    For i = lr4 To 2 Step -1
        If rng2.Cells(i, 11) Like "*4'dia**Invert*" Then
            If Not rng2.Cells(i, 14) Like "*Toho*" Then
                rng2.Cells(i, 11).Offset(1).EntireRow.Insert
                rng2.Cells(i, 3).Offset(1).Resize(1, 9).Value = _
                    Array("1", "F14050J", "Yes", "", "", "185", "Production", "500", "FLOWLINE,4' Diameter")
                rng2.Cells(i, 1).Offset(1).Resize(1, 2).Value = rng2.Cells(i, 1).Resize(1, 2).Value
            Else
                rng2.Cells(i, 11).Offset(1).EntireRow.Insert
                rng2.Cells(i, 3).Offset(1).Resize(1, 9).Value = _
                    Array("1", "F14050XJ", "Yes", "", "", "185", "Production", "500", "FLOWLINE,4' Diameter")
                rng2.Cells(i, 1).Offset(1).Resize(1, 2).Value = rng2.Cells(i, 1).Resize(1, 2).Value
            End If
        End If
       
        If rng2.Cells(i, 11) Like "*5'dia**Invert*" And _
            Not rng2.Cells(i, 14) Like "*Toho*" Then
                rng2.Cells(i, 11).Offset(1).EntireRow.Insert
                rng2.Cells(i, 3).Offset(1).Resize(1, 9).Value = _
                    Array("1", "F15050J", "Yes", "", "", "150", "Production", "500", "FLOWLINE,5' Diameter")
                rng2.Cells(i, 1).Offset(1).Resize(1, 2).Value = rng2.Cells(i, 1).Resize(1, 2).Value
        End If
    Next i
End Sub

 

[zack8576]

See if this works for you.
I think it is a simpler structure and easier to follow:

I assume you the variables set to Public for a reason, so I haven't changed that.

Sub Flowline()
    Set rng2 = Range("A1").CurrentRegion
    lr4 = rng2.Cells(Rows.Count, "K").End(3).Row
       
    For i = lr4 To 2 Step -1
        If rng2.Cells(i, 11) Like "*4'dia**Invert*" Then
            If Not rng2.Cells(i, 14) Like "*Toho*" Then
                rng2.Cells(i, 11).Offset(1).EntireRow.Insert
                rng2.Cells(i, 3).Offset(1).Resize(1, 9).Value = _
                    Array("1", "F14050J", "Yes", "", "", "185", "Production", "500", "FLOWLINE,4' Diameter")
                rng2.Cells(i, 1).Offset(1).Resize(1, 2).Value = rng2.Cells(i, 1).Resize(1, 2).Value
            Else
                rng2.Cells(i, 11).Offset(1).EntireRow.Insert
                rng2.Cells(i, 3).Offset(1).Resize(1, 9).Value = _
                    Array("1", "F14050XJ", "Yes", "", "", "185", "Production", "500", "FLOWLINE,4' Diameter")
                rng2.Cells(i, 1).Offset(1).Resize(1, 2).Value = rng2.Cells(i, 1).Resize(1, 2).Value
            End If
        End If
       
        If rng2.Cells(i, 11) Like "*5'dia**Invert*" And _
            Not rng2.Cells(i, 14) Like "*Toho*" Then
                rng2.Cells(i, 11).Offset(1).EntireRow.Insert
                rng2.Cells(i, 3).Offset(1).Resize(1, 9).Value = _
                    Array("1", "F15050J", "Yes", "", "", "150", "Production", "500", "FLOWLINE,5' Diameter")
                rng2.Cells(i, 1).Offset(1).Resize(1, 2).Value = rng2.Cells(i, 1).Resize(1, 2).Value
        End If
    Next i
End Sub

Alex, thank you.
I've tested this on 3 seperate excel files, it works beautifully ! very awesome !

 

[zack8576]

It may be that you are never satisfying some of your "If" conditions. You could use the VBA debugger to set breakpoints at each row insert statement, then single-step (F8) from that point to observe what happens.

BTW, if you post a graphic image of your data, the number of people willing to manually type in your data to experiment to help you will be limited. Consider using this free tool instead to post some sample data.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

thank you riv for the reply, I've uploaded a test file for others to see. You can download it from the dropbox link

2022080011 SANITARY.csv

Shared with Dropbox

www.dropbox.com

 

[Alex Blakenburg]

Alex, thank you.
I've tested this on 3 seperate excel files, it works beautifully ! very awesome !

Thanks for letting us know. Glad we could help.