combining two VBA codes

[steve400243]

Hello, I need to have a way for both codes to work together in my worksheet. They both work separately. Please see below code and Thanks for all help provided. It is really great help.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Only run when a single cell is updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   Check to see if column C or E updated
    If Target.Column = 3 Or Target.Column = 5 Then
'       Update cell to right
        Target.Offset(0, 1) = Now()
    End If
    -------------------------------------------------------------------------------------

  If Not Intersect(Target, Range("A:A")) Is Nothing Then
     If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
     Dim Lastrow As Long
     Dim Lastrowa As Long
     Lastrow = Sheets("shipped").Cells(Rows.Count, "A").End(xlUp).Row + 1
    

     If Target.Value = "shipped" Then
        Target.Resize(, 14).Copy
        If Target.Value = "shipped" Then Sheets("shipped").Range("A" & Lastrow).PasteSpecial (xlPasteValues)
                 Application.EnableEvents = False
          Rows(Target.Row).Delete
        Application.EnableEvents = True
     End If
  End If
End Sub

 

[Joe4]

If they both work inidividually, I do not see any reason why they wouldn't work when both are placed together (one under the other) in a single "Worksheet_Change" event procedure.

How is it not working?
Can you walk us through an actual example where it is not working?

 

[steve400243]

If they both work inidividually, I do not see any reason why they wouldn't work when both are placed together (one under the other) in a single "Worksheet_Change" event procedure.

How is it not working?
Can you walk us through an actual example where it is not working?

Hello Joe4, thank you for looking at it. When cell A3, A4, etc changes to "Shipped" using a cell formula it should move the row to the Shipped Tab. I may be doing something wrong, but it is not working for me. I tried like this, but get a compile error.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Only run when a single cell is updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   Check to see if column C or E updated
    If Target.Column = 3 Or Target.Column = 5 Then
'       Update cell to right
        Target.Offset(0, 1) = Now()
 If Not Intersect(Target, Range("A:A")) Is Nothing Then
     If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
     Dim Lastrow As Long
     Dim Lastrowa As Long
     Lastrow = Sheets("shipped").Cells(Rows.Count, "A").End(xlUp).Row + 1
    

     If Target.Value = "shipped" Then
        Target.Resize(, 14).Copy
        If Target.Value = "shipped" Then Sheets("shipped").Range("A" & Lastrow).PasteSpecial (xlPasteValues)
                 Application.EnableEvents = False
          Rows(Target.Row).Delete
        Application.EnableEvents = True
     End If
  End If
End Sub

 

[Joe4]

When cell A3, A4, etc changes to "Shipped" using a cell formula it should move the row to the Shipped Tab.

Worksheet_Change event procedures do NOT run when cells are changed via formula!
They only run when cells are manually updated (manual entry, copy/paste).

If cells A3 and A4 contain formulas, then you have a few other options.
- If the formulas contain references to other cells that are being updated manually, write your code to run when those values are manually changed.
- Create a "Worksheet_Calculate" event procedure, which runs whenever a value on the sheet is updated. The caveat here is that there is no "Target" cell in a "Worksheet_Calculate" event procedure. Excel has NO idea what cell was updated, just that one was somewhere on the sheet. So if you use this option, you cannot limit it to just the cell that was changed.

 

[steve400243]

Worksheet_Change event procedures do NOT run when cells are changed via formula!
They only run when cells are manually updated (manual entry, copy/paste).

If cells A3 and A4 contain formulas, then you have a few other options.
- If the formulas contain references to other cells that are being updated manually, write your code to run when those values are manually changed.
- Create a "Worksheet_Calculate" event procedure, which runs whenever a value on the sheet is updated. The caveat here is that there is no "Target" cell in a "Worksheet_Calculate" event procedure. Excel has NO idea what cell was updated, just that one was somewhere on the sheet. So if you use this option, you cannot limit it to just the cell that was changed.

"- If the formulas contain references to other cells that are being updated manually, write your code to run when those values are manually changed" How would this be written?

Here is the formula I am using in Column A.

=IF(D3="","",IF(F3="","Received","Shipped"))

 

[Joe4]

"- If the formulas contain references to other cells that are being updated manually, write your code to run when those values are manually changed" How would this be written?

Here is the formula I am using in Column A.

=IF(D3="","",IF(F3="","Received","Shipped"))

So if you were to use a Worksheet_Change code, it looks like you want your code to run off of changes to columns D and F, so you would structure very similarly to how we did the first one, with columns C and E.

Note that we can trigger it to run off of changes to columns D and F, but still look at the value in column A.

 

[steve400243]

So if you were to use a Worksheet_Change code, it looks like you want your code to run off of changes to columns D and F, so you would structure very similarly to how we did the first one, with columns C and E.

Note that we can trigger it to run off of changes to columns D and F, but still look at the value in column A.

I think if the code was triggered to run just off Column A when changed to "Shipped" would be ideal. Would I change the code to something like this? Thanks a lot for your assistance my VBA Skills are still in the beginner. So much to learn.

 If Target.Column = 1

 

[Joe4]

I think if the code was triggered to run just off Column A when changed to "Shipped" would be ideal. Would I change the code to something like this? Thanks a lot for your assistance my VBA Skills are still in the beginner. So much to learn.

 If Target.Column = 1

I suggest that you co back and read post 4 again.
That will NOT work if column A contains formulas! As I said, Worksheet_Change event procedures do NOT work on changes made by formulas!
They only work on manually updated data.

Since the formula looks at columns D and F, then you would need to do it the same way we did the other one I helped you with yesterday, i.e.

If Target.Column = 4 or Target.Columns = 6 Then

Then, once inside that IF...THEN block, you can check the values in any columns of the Target row like this, for example:

'Column A
If (Cells(Target.Row, "D") = "") And (Cells(Target.Row, "F") = "") And (Cells(Target.Row, "A") = "Shipped") Then

 

[steve400243]

I suggest that you co back and read post 4 again.
That will NOT work if column A contains formulas! As I said, Worksheet_Change event procedures do NOT work on changes made by formulas!
They only work on manually updated data.

Since the formula looks at columns D and F, then you would need to do it the same way we did the other one I helped you with yesterday, i.e.

If Target.Column = 4 or Target.Columns = 6 Then

Then, once inside that IF...THEN block, you can check the values in any columns of the Target row like this, for example:

'Column A
If (Cells(Target.Row, "D") = "") And (Cells(Target.Row, "F") = "") And (Cells(Target.Row, "A") = "Shipped") Then

"They only work on manually updated data." I'm sorry but I missed that part when reading your post the first time. I will work with what you have provided a bit later today but I may reach back out for assistance on this post so I will keep it open for now. Thank you again for your time, and skills.

 

[Joe4]

You are welcome.