Need help with VBA coding If and ElseIf statements

[ubergreen]

I currently have a code that roughly looks like below.

If condition1 Then
   [ statement1 ] 

ElseIf condition2 Then
  [ statement2 ]

End If



If condition3 Then
  [ statement3 ]

End If

At the moment, if I run this code, it will successfully execute on a row that meets condition1, but will skip condition2 and go straight to executing rows that meets condition3.

I want the code to execute all conditions, 1, 2, and 3. However, from what it seems like is happening is that as soon as condition1 is satisfied, it will then simply jump to check for condtion3.

I am trying to modify an original code that had only condition1 and condition2, which required it to be set up as If and ElseIf for it to work properly.

I thought if I simply added on the If condition3 at the end then it would still execute the original conditions correctly and then do the new additional condition3 afterwards, but that doesn't seem to be the case.

 

[dreid1011]

The way that code works is it will check Condition1 and execute if TRUE, then check Condition3 and execute if TRUE. The only way Condition2 is checked is if Condition1 is FALSE. If you want all the conditions to be checked every time, you should have separate IF/THEN statements for each condition, not nested.

 

[ubergreen]

The way that code works is it will check Condition1 and execute if TRUE, then check Condition3 and execute if TRUE. The only way Condition2 is checked is if Condition1 is FALSE. If you want all the conditions to be checked every time, you should have separate IF/THEN statements for each condition, not nested.

I tried expanding my code in to separate if/then statements, but then it doesn't end up working properly for what I need it to do.

Here's a better example of what I am trying to achieve below. My original code with the nested if/then statements looked like this below. The rows highlighted in green are the new rows that were added after running the function.

Sub new_rows()

For i = 2 To 1000

    tax1= Cells(i, 3).Value
    tax2= Cells(i, 4).Value

  
    If tax1 <> "" And tax1 <> 0 And tax2 <> "" and tax2 <> 0 Then
    Cells(i, 1).Offset(0, 0).EntireRow.Insert
    Cells(i, 1).Offset(0, 0).EntireRow.Insert
    Cells(i, 5) = tax1
    i = i + 1

    Cells(i, 5) = tax2
    i = i + 2


    ElseIf tax1 <> "" And tax1 <> 0 Then
    Cells(i, 1).Offset(0, 0).EntireRow.Insert
    Cells(i, 5) = tax1
    i = i + 2
    End If


Next
End Sub

However, I ended up needing to add an additional column, Shipping, and this is what I was hoping it would look like with the new code containing all 3 conditions. The purple rows is what I want it to look like with condition3 added in to the original function.

 

[dreid1011]

Okay, give this a try:
This does not fill in the col A values.

Sub new_rows()
Dim tax1 As Variant, tax2 As Variant, ship As Variant
Dim i As Long, lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lRow
    tax1 = Cells(i, 3).Value
    tax2 = Cells(i, 4).Value
    ship = Cells(i, 6).Value
  
    If tax1 <> "" And tax1 <> 0 And tax2 <> "" And tax2 <> 0 Then
        Cells(i, 1).EntireRow.Resize(2).Insert
        Cells(i, 5) = tax1
        i = i + 1
        Cells(i, 5) = tax2
        i = i + 1
    ElseIf tax1 <> "" And tax1 <> 0 Then
        Cells(i, 1).EntireRow.Insert
        Cells(i, 5) = tax1
        i = i + 2

    End If
    If ship <> "" And ship <> 0 Then
        Cells(i + 1, 1).EntireRow.Insert
        Cells(i + 1, 5) = ship
        i = i + 2
    End If
Next
End Sub

 

[ubergreen]

Okay, give this a try:
This does not fill in the col A values.

Sub new_rows()
Dim tax1 As Variant, tax2 As Variant, ship As Variant
Dim i As Long, lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lRow
    tax1 = Cells(i, 3).Value
    tax2 = Cells(i, 4).Value
    ship = Cells(i, 6).Value
 
    If tax1 <> "" And tax1 <> 0 And tax2 <> "" And tax2 <> 0 Then
        Cells(i, 1).EntireRow.Resize(2).Insert
        Cells(i, 5) = tax1
        i = i + 1
        Cells(i, 5) = tax2
        i = i + 1
    ElseIf tax1 <> "" And tax1 <> 0 Then
        Cells(i, 1).EntireRow.Insert
        Cells(i, 5) = tax1
        i = i + 2

    End If
    If ship <> "" And ship <> 0 Then
        Cells(i + 1, 1).EntireRow.Insert
        Cells(i + 1, 5) = ship
        i = i + 2
    End If
Next
End Sub

Thanks! I see you changed a few things around. I am still fairly new to VBA, so I'll try to understand the new methods/names that you used. It looks like it is working though. I have a larger/more complicated spreadsheet that I actually am applying this concept to. I'll test it out and will come back to confirm if everything is working as expected.

 

[ubergreen]

After being able to test it some more, I noticed that there were a few issues that I wasn't able to figure out. It seems like the code is skipping certain rows every time. For example, it is always skipping the original row that order # 1007 and 1001 are in.

This is what it currently looks like when I run the code as is below. I highlighted the cells that were supposed to be detected by the code but nothing happened when it was ran. It basically skipped row 4 (order # 1007) and row 14 (order # 1001)

Another sample test below. For this example, I added a value into Tax 1 for order # 1002, which worked properly. However, I also added another row of data at the very bottom, order # 100, and it seem to have work, but for some reason added an extra blank row between the original row and the Tax 3 value that was Duplicated from Ship.

Okay, give this a try:
This does not fill in the col A values.

Sub new_rows()
Dim tax1 As Variant, tax2 As Variant, ship As Variant
Dim i As Long, lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lRow
    tax1 = Cells(i, 3).Value
    tax2 = Cells(i, 4).Value
    ship = Cells(i, 6).Value
 
    If tax1 <> "" And tax1 <> 0 And tax2 <> "" And tax2 <> 0 Then
        Cells(i, 1).EntireRow.Resize(2).Insert
        Cells(i, 5) = tax1
        i = i + 1
        Cells(i, 5) = tax2
        i = i + 1
    ElseIf tax1 <> "" And tax1 <> 0 Then
        Cells(i, 1).EntireRow.Insert
        Cells(i, 5) = tax1
        i = i + 2

    End If
    If ship <> "" And ship <> 0 Then
        Cells(i + 1, 1).EntireRow.Insert
        Cells(i + 1, 5) = ship
        i = i + 2
    End If
Next
End Sub

 

[dreid1011]

Okay, the first issue can be solved by changing these lines:

i = i + 2

to this:

i = i + 1

The second issue is because I changed the original code to identify the last used row in column A and set that as the end of the loop, but during the loop, the size of the range is changing and the end of the loop is not accounting for that, so it reaches the end of the loop before it reaches the modified end of the data. This can be resolved by using a large number like you originally had set:

For i = 2 To 1000

However, this number needs to be able to account for all the possible additional rows you might add. I suppose one way to do that is multiply the last row by 4 since you add at most 3 new rows for any entry:

lRow = Cells(Rows.Count, 1).End(xlUp).Row * 4