如何为拆分后的每日缺勤记录添加事件编号？VBA代码优化求助

Hey there! I see you've already nailed the core logic to split absence records into one row per day—nice work! Let's tweak your existing code so each original absence event gets a unique ID that carries over to all its daily rows, just like the orange-marked column you referenced.

What We'll Implement: #

• Add a dedicated column for the unique event ID
• Assign the same ID to every daily row generated from a single original absence record
• Increment the ID automatically for each new absence event

Modified VBA Code: #

Sub One_Day_Per_Row_With_EventID()
 Dim a, b
 Dim rws As Long, sr As Long, i As Long, j As Long, k As Long, r As Long
 Dim eventID As Long ' New variable to track unique event IDs
 
 a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 6).Value
 rws = UBound(a, 1)
 
 ' Calculate total number of rows needed after splitting
 For r = 1 To rws
 a(r, 6) = a(r, 5) - a(r, 4) + 1
 k = k + a(r, 6)
 Next r
 
 If k < Rows.Count Then
 ReDim b(1 To k, 1 To 5) ' Resize array to include event ID column (5 columns total)
 sr = 1
 eventID = 1 ' Initialize event ID counter starting at 1
 
 For r = 1 To rws
 ' Assign the same event ID to all daily rows of this absence event
 For i = 0 To a(r, 6) - 1
 b(sr + i, 1) = eventID ' Populate the event ID column
 For j = 1 To 3
 b(sr + i, j + 1) = a(r, j) ' Shift original employee data right by 1 column
 Next j
 b(sr + i, 5) = a(r, 4) + i ' Date column (now the 5th column in the output)
 Next i
 
 sr = sr + a(r, 6)
 eventID = eventID + 1 ' Increment ID for the next absence event
 Next r
 
 ' Output the final split data with headers
 Range("G2").Resize(k, 5).Value = b
 Range("G1:K1").Value = Array("event ID", "emp number", "emp name", "absence code", "date")
 Else
 MsgBox "Too many rows to process—please check your data!"
 End If
End Sub

Key Changes Breakdown: #

• eventID variable: This tracks the unique identifier for each original absence event, starting at 1 and incrementing after we finish processing each event.
• Resized output array: We expanded the b array from 4 to 5 columns to make space for the new event ID column.
• Consistent ID per event: Every daily row generated from a single absence record gets the same eventID value, so all rows linked to the same event share the same identifier.
• Shifted data columns: Employee number, name, and absence code are now shifted right by one column to accommodate the event ID in the first position of the output.

This will give you exactly the numbered column you're looking for—each distinct absence event (your yellow/green rows) will have a unique ID that applies to all its split daily entries.

内容的提问来源于stack exchange，提问作者nenoriu