Inserting date based on status of load

[sonofagunn123]

Hopefully someone in here can help me.. I am currently trying to update our Shipping/ Load tracker and I am running into an issue. I am trying to make it to where when column N shows as "Packing" it enters the date and time into column P and when it shows "Packed" enters the date and time into column Q. The problem is once I change it from packing to packed it erases the previous date and time stamp. thanks in advance for yall's help.

 

[SanjayGMusafir]

Hopefully someone in here can help me.. I am currently trying to update our Shipping/ Load tracker and I am running into an issue. I am trying to make it to where when column N shows as "Packing" it enters the date and time into column P and when it shows "Packed" enters the date and time into column Q. The problem is once I change it from packing to packed it erases the previous date and time stamp. thanks in advance for yall's help. View attachment 71378

Since it's a VBA problem and I'm quite layman at it - I think of an idea and then try to find right VBA method to execute that.

What I can tell is that it can be solved by add an IF statement where it skips changing information in Column P if input in Column N anything but "Packing"

Post that part of VBA here, where it inputs Date & Time stamp in Column P on the basis of Input in Column N and someone shall help you out.

 

[sonofagunn123]

Since it's a VBA problem and I'm quite layman at it - I think of an idea and then try to find right VBA method to execute that.

What I can tell is that it can be solved by add an IF statement where it skips changing information in Column P if input in Column N anything but "Packing"

Post that part of VBA here, where it inputs Date & Time stamp in Column P on the basis of Input in Column N and someone shall help you out.

The if statement that i have is =IF(N128="Packing",NOW(),"") , =IF(N128="Packed",NOW(),"").. I dont have a vba for this was hoping someone here might know of one or have a formula that will work.

 

[Joe4]

How are the values in column N being updated?
Is someone manually doing that?

If so, then we want to use "Worksheet_Change" event procedure code to automatically run when column N is automatically being updated.

To do this, right-click on the sheet tab name at the bottom of your screen, select "View Code", and paste this code in the VB Editor window that pops up.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   See if any cells in column N updated
    Set rng = Intersect(Target, Range("N:N"))
    If rng Is Nothing Then Exit Sub
    
'   Loop through updated cells in column N
    For Each cell In rng
        Select Case cell.Value
            Case "Packing"
                Range("P" & cell.Row).Value = Now()
            Case "Packed"
                Range("Q" & cell.Row).Value = Now()
        End Select
    Next cell
    
End Sub

 

[sonofagunn123]

How are the values in column N being updated?
Is someone manually doing that?

If so, then we want to use "Worksheet_Change" event procedure code to automatically run when column N is automatically being updated.

To do this, right-click on the sheet tab name at the bottom of your screen, select "View Code", and paste this code in the VB Editor window that pops up.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
   
'   See if any cells in column N updated
    Set rng = Intersect(Target, Range("N:N"))
    If rng Is Nothing Then Exit Sub
   
'   Loop through updated cells in column N
    For Each cell In rng
        Select Case cell.Value
            Case "Packing"
                Range("P" & cell.Row).Value = Now()
            Case "Packed"
                Range("Q" & cell.Row).Value = Now()
        End Select
    Next cell
   
End Sub

This worked perfectly thank you very much! you saved me quite the headache!

 

[Joe4]

You are welcome.
Glad I was able to help!