How to Update an Existing Excel Row with an End Time Using VBA
Question details
The user wants to find a specific transfer number in an existing Excel dataset and record the current completion time in the same row, avoiding the creation of a new row.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Logging completion times for specific tasks or transfers using macros without duplicating records.
- Observed behavior
- The user needs a dynamic script to search for an ID and append the Now() timestamp to the correct existing row rather than generating a new entry.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your Trust Center settings before running new VBA scripts.
Use a VBA Macro to Find and Update the Row
Use a VBA loop to search for the target ID in a specific column and insert the current date and time into an adjacent column on the exact same row.
This VBA script searches through your specified column for a matching transfer number. Once found, it updates a target column (like Column B) with the current timestamp using the Now() function, then immediately exits the loop to ensure efficiency.
Open your Excel workbook and press `Alt + F11` to launch the Visual Basic for Applications (VBA) Editor.
In the top menu, go to `Insert` > `Module` to create a blank workspace for your script.
Copy and paste the following code: `Sub UpdateEndTime() Dim ws As Worksheet Dim lastRow As Long Dim transferNum As String Dim cell As Range Set ws = ThisWorkbook.Sheets("Data") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row transferNum = ActiveCell.Value For Each cell In ws.Range("A2:A" & lastRow) If cell.Value = transferNum Then ws.Cells(cell.Row, 2).Value = Now(): Exit Sub End If Next cell MsgBox "Transfer number not found!", vbExclamation End Sub`
Modify the sheet name `Sheets("Data")`, the search range `"A2:A"`, and the column index `2` (which stands for Column B) to match your actual spreadsheet layout.
Close the VBA Editor. Select a cell containing the transfer number you want to complete, press `Alt + F8`, select `UpdateEndTime`, and click `Run`.
Run VBA Macros Seamlessly in WPS Spreadsheet
WPS Office Spreadsheet fully supports VBA macros, allowing you to automate tasks like updating timestamps just as you would in Microsoft Excel.
- 1. Download and Install WPS Office: Get the latest version of WPS Office from the official website and install it on your device.
- 2. Open Your Spreadsheet: Open your Macro-Enabled Workbook (.xlsm) using WPS Spreadsheet.
- 3. Access the Developer Tools: Navigate to the `Developer` tab on the top ribbon menu to access macro features.
- 4. Manage and Run Macros: Click on `Macros` or `Visual Basic` to insert your row-updating script and execute your automated tasks.
Frequently Asked Questions
Why isn't my VBA code updating the correct row?
This usually happens if the search variable (like `transferNum`) doesn't exactly match the cell format in your column. Check for leading or trailing spaces, or mismatched data types (such as text formatted as numbers).
How can I format the inserted end time to show only the time and not the date?
You can format the target column directly in your spreadsheet to "Time", or add a VBA formatting line like `ws.Cells(cell.Row, 2).NumberFormat = "hh:mm:ss AM/PM"` right after inserting the `Now()` value.
Can I use VLOOKUP or XLOOKUP instead of VBA for this?
Standard formulas like `VLOOKUP` are dynamic and update continuously when the worksheet recalculates. This isn't ideal for a static timestamp. VBA is required to hardcode the exact time a task was completed so it doesn't change later.
What happens if the transfer number is not found in the list?
In the provided script, if the loop finishes checking the entire column without finding a match, a message box will appear stating "Transfer number not found!" to alert you of the failure.
