Modify the value in a cell based on multiple variables

zack8576

Active Member
Joined
Dec 27, 2021
Messages
271
Office Version
  1. 365
Platform
  1. Windows
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 !


VBA Code:
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

Capture.PNG
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Everything is working as intended ....
.. when you have a Compile error!?

You're missing an End If:

Rich (BB code):
' ....
            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?

VBA Code:
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.
 
Upvote 0
.. when you have a Compile error!?

You're missing an End If:

Rich (BB code):
' ....
            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?

VBA Code:
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

VBA Code:
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?
1663372350752.png
 
Upvote 0
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 ...

VBA Code:
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?
Code:
If A Then
    If B Then
        'do Y
    Else
        'Do X
    End If
'...
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,021
Latest member
pingpong7117

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top