Shift cells down based on corresponding cell content

[chcoder]

Hi
I'm trying to loop through my sheet and say that if the cell in row J says "segment missing doc", then shift down the corresponding lines L-S cells.
(and then recalculate the formula in column K)

I tried something like this..

sub test()

Dim Lastrow As Long
Dim cell As Long

For cell = Range("J" & Rows.Count).End(xlUp).row

If cell.Value = "segment missing doc" Then

Range("L:S").Insert Shift:=xlDown

Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K" & Lastrow)

Range("V2").Select
Selection.AutoFill Destination:=Range("V2:V" & Lastrow)


End If
Next

End sub ()

 

[Fluff]

Hi & welcome to MrExcel.
how about

Sub test()

   Dim Lastrow As Long, i As Long

   Lastrow = Range("J" & Rows.Count).End(xlUp).Row
   For i = Lastrow To 2 Step -1
      If Range("J" & i).Value = "segment missing doc" Then
         Range("L" & i).Resize(, 8).Insert xlDown
         Lastrow = Lastrow + 1
         Range("K2").AutoFill Destination:=Range("K2:K" & Lastrow)
         Range("V2").AutoFill Destination:=Range("V2:V" & Lastrow)
      End If
   Next i
End Sub

 

[chcoder]

not sure what i'm doing wrong but nothing happens when i run this..

also this line was in my original code but is it a mistake..

Lastrow = Range("J" & Rows.Count).End(xlUp).Row

because you made another statement

Lastrow = Lastrow + 1

 

[chcoder]

not sure what i'm doing wrong but nothing happens when i run this..

also this line was in my original code but is it a mistake..

Lastrow = Range("J" & Rows.Count).End(xlUp).Row

because you made another statement

Lastrow = Lastrow + 1

sorry, actually got it to run i'll look through this.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[chcoder]

So I just tried it but I don't want to insert a line, I want to shift everything down..
so I think this line needs adjusting..?
Range("L" & i).Resize(, 8).Insert xlDown

 

[Fluff]

If you want to shift everything down, then you are inserting rows.
You said

if the cell in row J says "segment missing doc", then shift down the corresponding lines L-S cells.

Is that not what you want?

 

[chcoder]

oh ok yes, I get it now

 

[Fluff]

Is the code doing what you wanted?

 

[chcoder]

It worked for the first occurrence, but then for some reason in the next occurences, it just inserted a line below the line noted "segment missing doc"
also sometimes there will be a couple of "segment missing doc" in a row i would need everything shifted down until the next cell that does not say "segment missing doc.