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!!

 

[ncheatherp]

It runs without stopping.... I don't get an error. But, it doesn't insert rows unless it is inserting after the last row of data? If that's the case, I need to insert blank rows between rows of data. There is data in rows 19 - 65. So if the Ys are in Row 19, I need to insert the rows between 19 and 20. Does that make sense?

 

[texasalynn]

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

Actually you need to have the test on the range each time

If Cells(i, "X") = "Y" or Cells(i, "X") = "y" Then

 

[RSXchin]

last try

Sub insert()
'
' insert Macro
'

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

 

[ncheatherp]

Gosh y'all....both of you gave me very similar code and neither worked for me for some reason. I'm not getting an error....but it isn't inserting rows.

I'm working in Office 2010...should that have any impact on it?

 

[RSXchin]

if it isn't inserting rows... what is it doing? it works just fine on my office, which is also 2010. did you try my last macro?

 

[RSXchin]

is there a CHANCE that "W" is empty in your spreadsheet?

 

[ncheatherp]

Yes, I tried it. W is empty in rows 1-17...but the column heading is in W18 and the Ys and Ns begin in W19.

 

[ncheatherp]

...and to answer your other question, when I run it, nothing happens. My cursor stays where it was before I ran it and no new rows are inserted.

 

[RSXchin]

you're saying this code doesn't work for you... at all? i assure you, this code works. and I'm relatively sure it works for TexasLynn too.

what do you mean, doesn't move your cursor. it isn't supposed to. it adds rows.

 

[ncheatherp]

I believe you!! I won't take anymore or your time. I must just be doing something wrong. I'll get it eventually. Thank you both for your help.