Forum Discussion

CatherineMadden's avatar
CatherineMadden
Copper Contributor
Jun 03, 2025

Adding an end time to existing row using VBA

I have the VBA set up to enter the date and time for when they start an order, but I need help for when they finish an order.

When they finish the order and click the "Finish" button, I need the VBA to look for the original transfer number in the Data tab then return the end date and time (yellow). I realized that with the VAB I have now, it is starting a new line and entering the end time.

Attaching the file. Thank you in advance.

excel
Macros and VBA

2 Replies

Sort By
  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Jun 04, 2025

    How about this:

     

    Sub UpdateEndTime()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim transferNum As String
    Dim cell As Rangexzz
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Data") ' Change "Data" to your actual sheet name
    
    ' Get the last row in the sheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Get the transfer number from the active row
    transferNum = ActiveCell.Value ' Modify this based on where the transfer number is stored
    
    ' Loop through column A to find the matching transfer number
    For Each cell In ws.Range("A2:A" & lastRow) ' Assuming transfer numbers are in column A
        If cell.Value = transferNum Then
            ' Update the End Time column (assuming it's column B)
            ws.Cells(cell.Row, 2).Value = Now() ' Change column index if needed
            Exit Sub
        End If
    Next cell
    
    MsgBox "Transfer number not found!", vbExclamation
End Sub

     

Resources