Inserting Rows with IF/THEN in a macro

[ncheatherp]

I'm trying to figure out how to write the following logic inside a macro:

IF W19=Y, THEN INSERT 2 ROWS BELOW R19
IF W19=Y AND X19=Y, THEN INSERT 5 ROWS BELOW R19
Then I would like it to loop to the next row of data and do the same thing (until it gets thru each row of data). For example, if 5 Rows were added after R19, it would then look at W26 & X26 next.

I know that anything is possible....this just has me stumped!!

 

[RSXchin]

Welcome to the board.

Does this loop start on w19, or does it start at w1 or something? if w19=y and x19=y, then you want to add 7 rows?

 

[texasalynn]

Welcome to MrExcel Board...

are you truly inserting rows or just cells? The reason I ask is because you say to insert rows at a cell location?

 

[RSXchin]

If you're adding rows, try this one out.

Sub insert()
'
' insert Macro
'

'
LastRow = Cells(Rows.Count, 23).End(xlUp).Row
For i = LastRow To 1 Step -1
   If Range("w" & i).Value = "Y" Then
      Rows(i + 1).insert shift:=xlDown
      Rows(i + 1).insert shift:=xlDown
         If Range("x" & i).Value = "Y" Then
            Rows(i + 1).insert shift:=xlDown
            Rows(i + 1).insert shift:=xlDown
            Rows(i + 1).insert shift:=xlDown
            Rows(i + 1).insert shift:=xlDown
            Rows(i + 1).insert shift:=xlDown
        End If
    End If
Next i
End Sub

 

[texasalynn]

here is my version

Sub InsertRows()
Dim LR As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 19 Step -1
If Cells(i, "X") = "Y" And Cells(i, "W") = "Y" Then
    Rows(i & ":" & i + 5).EntireRow.Insert
    ElseIf Cells(i, "W") = "Y" Then
    Rows(i & ":" & i + 2).EntireRow.Insert
End If
Next i
 
End Sub

 

[RSXchin]

whoops, you substituted "R" for "X" in your if statements

 

[texasalynn]

whoops, you substituted "R" for "X" in your if statements

LOL - guess I saw that while you were typing your message - Thanks

 

[ncheatherp]

Thanks for the welcomes!

I truly want it to insert rows.

W (starting with W19) contains Ys and Ns (for Yes or No)
X (starting wtih X19) also contains Ys and Ns (for Yes or No)

If the value in W is equal to "Y" and the value in X is also equal to "Y", I want to insert 5 rows.

If the value in W is equal to "Y" and the value in X is "N", I only want to insert 2 rows.

I apologize if I seem to be a novice... I am! I volunteered to take on writing a macro to automatically create a spreadsheet that was built manually in the past.... a spreadsheet that took 20 hours per month to build manually. I've spent 2 days on this so far and am almost there. This issue has me stumped from going any further tho.

I tried the code you provided and it didn't work.

 

[texasalynn]

What code did you try? And what does the code do?

Are you sure the data in the cell is only "Y" or are there perhaps spaces?

 

[RSXchin]

Sub InsertRows()
Dim LR As Long
LR = ActiveSheet.Range("W" & Rows.Count).End(xlUp).Row
For i = LR To 19 Step -1
If Cells(i, "W") = "Y" or "y" Then
    Rows(i + 1 & ":" & i + 2).EntireRow.Insert
        If Cells(i, "X") = "Y" or "y" Then
             Rows(i + 1 & ":" & i + 3).EntireRow.Insert
        end if
End If
Next i
 
End Sub

there is his code, but with the appropriate tweak. NOW you can use upper or lower case Ys