Seems easy but losing my mind... Combine Worksheet_Change

[Drivium]

I have these two pieces of code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Value Like "Moved to last planner" Then

Sheets("Last Planner").Range("A" & Rows.Count).Find("").End(xlUp).Offset(1, 0) = Target.Offset(0, 2 - Target.Column()).Value
Else
If Target.Value <> "Moved to last planner" Then
End If
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
For i = 1 To 30
If Target.Value Like "Not Implemented" Then

If Sheets("Last Planner").Range("A" & i) = Target.Offset(0, 2 - Target.Column()).Value Then
Sheets("Last Planner").Range("A" & i) = ""
Else

End If
End If
Next i
End Sub

They both work great independently, but I need to somehow merge them to fit together under Worksheet_Change as 1 piece of code. Ive tried everything, but Im getting errors like "End if without block if", "object not defined", "Next without for"... TIA

 

[James006]

Hi,

You could have a first try with following :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
    If Target.Count > 1 Then Exit Sub
    If Target.Column > 1 Then Exit Sub

    If Target.Value Like "Moved to last planner" Then
        Sheets("Last Planner").Range("A" & Rows.Count).Find("").End(xlUp).Offset(1, 0) = Target.Offset(0, 2 - Target.Column()).Value
    Else
        If Target.Value Like "Not Implemented" Then
        For i = 1 To 30
         If Sheets("Last Planner").Range("A" & i) = Target.Offset(0, 2 - Target.Column()).Value Then Sheets("Last Planner").Range("A" & i) = ""
        Next i
        End If
    End If
End Sub

HTH

 

[Ruddles]

I would say: remove the last line of the first block of code and the first line of the last block of code, then stitch what's left of the two blocks together.

Another way of putting it: remove the first and last lines from the second block of code and insert what's left before the last line of the first block of code.

 

[Drivium]

Hi,

You could have a first try with following :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
    If Target.Count > 1 Then Exit Sub
    If Target.Column > 1 Then Exit Sub

    If Target.Value Like "Moved to last planner" Then
        Sheets("Last Planner").Range("A" & Rows.Count).Find("").End(xlUp).Offset(1, 0) = Target.Offset(0, 2 - Target.Column()).Value
    Else
        If Target.Value Like "Not Implemented" Then
        For i = 1 To 30
         If Sheets("Last Planner").Range("A" & i) = Target.Offset(0, 2 - Target.Column()).Value Then Sheets("Last Planner").Range("A" & i) = ""
        Next i
        End If
    End If
End Sub

HTH

I tried this, it doesn't error but it also seems to do nothing. In the column, there are a several cells where "moved to last planner" or "not implemented" may exist. In other words there are a range of cells in the column that might meet the criteria...

Maybe something in this stopping it from moving forward?

    If Target.Count > 1 Then Exit Sub
    If Target.Column > 1 Then Exit Sub

 

[Drivium]

I still can't get it... I even tried leaving the two pieces of code in tact and just creating another to call each of those if the change meets the criteria, but I get "Argument not optional" error:

Private Sub Moved_to_last_planner(ByVal Target As Range)

If Target.Value Like "Moved to last planner" Then

Sheets("Last Planner").Range("A" & Rows.Count).Find("").End(xlUp).Offset(1, 0) = Target.Offset(0, 2 - Target.Column()).Value
Else
If Target.Value <> "Moved to last planner" Then
End If
End If
End Sub

Private Sub Not_Implemented(ByVal Target As Range)
For i = 1 To 30
If Target.Value Like "Not Implemented" Then

If Sheets("Last Planner").Range("A" & i) = Target.Offset(0, 2 - Target.Column()).Value Then
Sheets("Last Planner").Range("A" & i) = ""
Else

End If
End If
Next i
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Value Like "Moved to last planner" Then
    Call Moved_to_last_planner
 Else
     If Target.Value Like "Not Implemented" Then
    Call Not_Implemented
    End If
              End Sub

 

[Ruddles]

Did you try my suggestion - post #3?

 

[Drivium]

Ruddles, I absolutely did, and thank you for it. As far as I could tell, I resulting code looked like it matched your suggestion, but it still generated errors.

 

[Drivium]

Ruddles - if I'm understanding correctly, this would be the resulting code:

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Value Like "Moved to last planner" Then

Sheets("Last Planner").Range("A" & Rows.Count).Find("").End(xlUp).Offset(1, 0) = Target.Offset(0, 2 - Target.Column()).Value
Else
If Target.Value <> "Moved to last planner" Then
End If
For i = 1 To 30
If Target.Value Like "Not Implemented" Then

If Sheets("Last Planner").Range("A" & i) = Target.Offset(0, 2 - Target.Column()).Value Then
Sheets("Last Planner").Range("A" & i) = ""
Else

End If
End If
Next i
End If
End Sub

When "Not Implemented" is selected, it works as it should, but when "Moved to last planner" is selected, it generates this error: Object variable or with block variable not set. It also highlights this piece of code:

Sheets("Last Planner").Range("A" & Rows.Count).Find("").End(xlUp).Offset(1, 0) = Target.Offset(0, 2 - Target.Column()).Value

The fact that at least a piece of this combo works is promising though!

 

[Norie]

Perhaps something like this.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Long

    Select Case Target.Value
        Case "Moved to last planner"

            Sheets("Last Planner").Range("A" & Rows.Count).Find("").End(xlUp).Offset(1, 0) = Target.Offset(0, 2 - Target.Column()).Value

        Case "Not Implemented"

            For I = 1 To 30

                If Sheets("Last Planner").Range("A" & I) = Target.Offset(0, 2 - Target.Column()).Value Then
                    Sheets("Last Planner").Range("A" & I) = ""
                End If
            Next I
    End Select
    
End Sub

 

[Drivium]

This case statement from Norie, and probably James006 statement WOULD have worked... I just realized a few attempts ago, I changed:

(GOOD CODE)

Sheets("Last Planner").Range("A:A").Find("").End(xlUp).Offset(1, 0) = Target.Offset(0, 2 - Target.Column()).Value

To (BAD CODE)

Sheets("Last Planner").Range("A" & Rows.Count).Find("").End(xlUp).Offset(1, 0) = Target.Offset(0, 2 - Target.Column()).Value

I have it working now with this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Long

    Select Case Target.Value
        Case "Moved to last planner"

            Sheets("Last Planner").Range("A:A").Find("").End(xlUp).Offset(1, 0) = Target.Offset(0, 2 - Target.Column()).Value

        Case "Not Implemented"

            For I = 1 To 30

                If Sheets("Last Planner").Range("A" & I) = Target.Offset(0, 2 - Target.Column()).Value Then
                    Sheets("Last Planner").Range("A" & I) = ""
                End If
            Next I
    End Select
    
End Sub

Thank you so much to all who contributed. Solved!