VBA Code Debug Help

[Siegmier]

OK so I have a code where if there is a S in a column it will delete the row of the S and move the row over to another sheet, then if there is a D in the column it will simply delete the row indicated, but once I inserted the D part I am getting an error where if I put an S in the column I then get a run time error after it completes the if then. I also get the same error when I insert a line.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False_
If Target.Column = 15 And Target.Value = "S" Then
'targets column O and if there is an S then
Rows(Target.Row).Copy _
Destination:=Worksheets("Shipped").Rows(Worksheets("Shipped") _
.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
Rows(Target.Row).Delete shift:=xlUp
End If
'Copy the row with the S paste it in one above the last used cell. Then delete said row and shift data up
If Target.Column = 15 And Target.Value = "D" Then
'targets column O and if there is an S then
Rows(Target.Row).Delete shift:=xlUp
End If
Application.EnableEvents = True
End Sub



the row that highlights when I debug it is the "If Target.Column = 15 And Target.Value = "D" Then" when it goes through the S script, but it highlights the S line when I insert a line.

Any help is much apriciated

-Sieg

EDIT: The runtime error is 424 if that helps

 

[Siegmier]

OK so using an ElseIf statement I got rid of the error when moving the line with S, however when I insert a line it still gives me runtime error 13

New Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False_
If Target.Column = 15 And Target.Value = "S" Then <------This line is highlighted in debugger
'targets column O and if there is an S then
Rows(Target.Row).Copy _
Destination:=Worksheets("Shipped").Rows(Worksheets("Shipped") _
.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
Rows(Target.Row).Delete shift:=xlUp
ElseIf Target.Column = 15 And Target.Value = "D" Then
'targets column O and if there is a D then
Rows(Target.Row).Delete shift:=xlUp
'Delete the row containing the D
End If
'Copy the row with the S paste it in one above the last used cell. Then delete said row and shift data up
Application.EnableEvents = True
End Sub

 

[NeonRedSharpie]

Using your new code, I didn't receive any errors. Can you highlight what line specifically is throwing the error?

 

[Siegmier]

@Neon, I made it bold, underlined it, and added a note to the side to point it out

PS the column you need to put the letter in is the 15th aka O, and I am currently getting the error only when I insert a line, otherwise it is now working fine(after I adjusted the if statement to an elseif)

 

[shg]

You can't compare an array (Target.Value, when you've changed more then one cell, like by inserting a row) to a single value. That's what's causing the runtime error.

If this only needs to work for single-cell changes, then

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Cells.Count = 1 And .Column = 15 Then
            On Error GoTo Oops
            Application.EnableEvents = False

            
            If .Value = "S" Then
                .EntireRow.Copy Worksheets("Shipped").Cells(Rows.Count, "A").End(xlUp)(2)
                .EntireRow.Delete
            ElseIf .Value = "D" Then
                .EntireRow.Delete
            End If
Oops:
            Application.EnableEvents = True
        End If
    End With
End Sub

 

[Siegmier]

Thank you so much shg that worked perfectly!

 

[shg]

You're welcome.

 

[Siegmier]

Is there a way that I can have it put today's date in Cell N when it moves the row?