3 different IF statements to detect cell values in Worksheet_Change

[rtaylor1987]

I'm working on a project where I need to be able to read data from different cells and sort these into different sheets depending on value.
Essentially, when you load data - it should be able to read the following:
Cases that are listed as active (If value = "active" then move to sheet Active)
cases that are pending (If Start Date is less than 30 days, it's flagged with a "y" in the system, so if Value="y" then move to "Pending"
cases that are already closed (If "closed date" is NOT null) then move them to the "closed" sheet

I have the Active part working, so I thought the code was fairly straight forward but it doesn't appear to like me pushing IF statements back-to-back. (I'm still fairly new to VB so it's most likely user error)

Any way to get each of these separate functions executed when worksheet change is detected?

Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Column = 28 And Target.Cells.Count = 1 Then
        If LCase(Target.Value) = "active" Then
            With Target.EntireRow
                .Copy Sheets("Active").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                .Delete
            End With
        End If
  End If
  If Target.Column = 18 And Target.Cells.Count = 1 Then
        If LCase(Target.Value) = "y" Then
            With Target.EntireRow
                .Copy Sheets("Pending").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                .Delete
            End With
        End If
  End If
  If Target.Column = 12 And Target.Cells.Count = 1 Then
        If Not LCase(Target.Value) = "null" Then
            With Target.EntireRow
                .Copy Sheets("Closed").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                .Delete
            End With
        End If
  End If
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
For the last part do you actually have the word null in col L or are you checking for a non empty cell?
Also in what way doesn't it work?

 

[rtaylor1987]

The cell itself will actually say "null" if the Closed Date is null in our system, so it is specifically looking for the word "null"

Using the script as is, I get an error about an expected statement. Changing the second and third to ElseIf fixes this, but it will not execute all three.

Essentially, people using this will be copy and pasting data from an exported file. I was hoping the data would then auto-filter out the pending and closed ones to their respective sheets, leaving the ones that are still awaiting a status. Changing the status to Active should move it to the Active sheet until that status changes as well.

 

[Fluff]

I get an error about an expected statement. Changing the second and third to ElseIf fixes this,

In that case the code you posted is not what you are using, as the code you posted is absolutely fine.
If you are copy/pasting data then you would probably be better of with a normal macro, as your code is designed to work when an individual cell is changed.

 

[rtaylor1987]

Apparently I had a typo that I fixed without realizing.
Trying the code again directly as posted above, and the error I'm getting is "Subscript out of range"

 

[Fluff]

On which line?

 

[rtaylor1987]

Line 9

 

[Fluff]

What is line 9?

 

[rtaylor1987]

  If Target.Column = 12 And Target.Cells.Count = 1 Then

 

[Fluff]

I don't see how you could get a subscript out of range error on that line.
I would expect it to be on one of the lines that starts with .Copy