Modify the value in a cell based on multiple variables

[zack8576]

I have this subroutine that adds an additional row after any row that contains certain keywords.
Everything is working as intended except for the second If statement (red text)
error message "Next without For" keeps poping up, see code below
Sample test file is also attached below. Please help !

Sub Flowline() 
    rng2 As Range 'this is defined as Public at the beginning of the macro
    lr4 As Long 'this is defined as Public at the beginning of the macro
    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
            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 
      [COLOR=rgb(184, 49, 47)]  If rng2.Cells(i, 11) Like "*4'dia**Invert*" And _
            rng2.Cells(i, 14) Like "*Toho Water*" 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 [/COLOR]
        ElseIf rng2.Cells(i, 11) Like "*5'dia**Invert*" 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 'This fills cols A&B with variable values
        End If
    Next i
End Sub

 

[StephenCrump]

Everything is working as intended ....

.. when you have a Compile error!?

You're missing an End If:

' ....
            ElseIf rng2.Cells(i, 11) Like "*5'dia**Invert*" 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 'This fills cols A&B with variable values
            End If
        End If
    Next i
End Sub

I assume you have Dim in front of these two lines if you're not getting a compile error here?

rng2 As Range 'this is defined as Public at the beginning of the macro
lr4 As Long 'this is defined as Public at the beginning of the macro

And if you do want these variables to be Public (?) you shouldn't be declaring them again inside a Sub.

 

[zack8576]

.. when you have a Compile error!?

You're missing an End If:

' ....
            ElseIf rng2.Cells(i, 11) Like "*5'dia**Invert*" 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 'This fills cols A&B with variable values
            End If
        End If
    Next i
End Sub

I assume you have Dim in front of these two lines if you're not getting a compile error here?

rng2 As Range 'this is defined as Public at the beginning of the macro
lr4 As Long 'this is defined as Public at the beginning of the macro

And if you do want these variables to be Public (?) you shouldn't be declaring them again inside a Sub.

Thank you! adding the End If fixed the issue. updated code below

Public Rng2 As Range, lr4 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*" 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 _
            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*" 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

But this also created a new issue, because test file row 297 meets criteria for both the first and the second if, it added 2 new rows
is there a way for the VBA to not add 2 rows ( in this case, do not add row 299), or ignore the first If statement, when a row meets all the criteria in the 2nd If statement?

 

[StephenCrump]

It's not clear to us what your intended logic is. You'll need to mentally step through the logic of your If blocks to check they're applying your tests in the right order.

In simple terms, your If blocks start ...

If A Then
    'Do X
    If A And B Then
        'do Y
'...

so if A and B are both True, the code will do X and Y.

Perhaps you intend?

If A Then
    If B Then
        'do Y
    Else
        'Do X
    End If
'...