求VBA代码：匹配Sheet1与BreakList名称并复制最后T编号至Sheet1

Alright, let's solve this VBA problem for you. The goal is to match names between Sheet1 and the BreakList worksheet, then copy the corresponding "T" column (last relevant column) value from BreakList to the matching row in Sheet1—ignoring those time period rows in BreakList since they're just for display.

Here's a robust, efficient macro that gets the job done:

Sub CopyMatchingTNumber()
    Dim wsBreakList As Worksheet
    Dim wsSheet1 As Worksheet
    Dim nameLookup As Object
    Dim lastRowBreakList As Long
    Dim lastRowSheet1 As Long
    Dim i As Long
    Dim currentName As String
    Dim tColumnValue As String
    
    ' Set your target worksheets (adjust names if your sheets have different labels)
    Set wsBreakList = ThisWorkbook.Worksheets("BreakList")
    Set wsSheet1 = ThisWorkbook.Worksheets("Sheet1")
    
    ' Use a dictionary for fast name lookups (avoids redundant row loops)
    Set nameLookup = CreateObject("Scripting.Dictionary")
    
    ' Find the last row with data in BreakList's name column (adjust column "A" if needed)
    lastRowBreakList = wsBreakList.Cells(wsBreakList.Rows.Count, "A").End(xlUp).Row
    
    ' Populate the dictionary with valid name-T value pairs
    For i = 1 To lastRowBreakList
        ' Skip time period/display-only rows (tweak this condition to match your sheet's layout)
        ' Example: skips blank names or rows that look like dates
        If wsBreakList.Cells(i, "A").Value <> "" And Not IsDate(wsBreakList.Cells(i, "A").Value) Then
            currentName = Trim(wsBreakList.Cells(i, "A").Value)
            tColumnValue = wsBreakList.Cells(i, "T").Value
            
            ' Add to dictionary (keeps the first occurrence of duplicate names; remove the check to keep last)
            If Not nameLookup.Exists(currentName) Then
                nameLookup.Add currentName, tColumnValue
            End If
        End If
    Next i
    
    ' Now match names in Sheet1 and paste the T column values
    lastRowSheet1 = wsSheet1.Cells(wsSheet1.Rows.Count, "A").End(xlUp).Row
    
    For i = 1 To lastRowSheet1
        currentName = Trim(wsSheet1.Cells(i, "A").Value)
        
        If nameLookup.Exists(currentName) Then
            ' Paste the T value into your desired column in Sheet1 (adjust "B" to your target column)
            wsSheet1.Cells(i, "B").Value = nameLookup(currentName)
        Else
            ' Optional: flag rows with no matching name
            wsSheet1.Cells(i, "B").Value = "No match found"
        End If
    Next i
    
    ' Clean up objects
    Set nameLookup = Nothing
    Set wsBreakList = Nothing
    Set wsSheet1 = Nothing
    
    MsgBox "Matching and copy complete!", vbInformation
End Sub

• Column/Worksheet Tweaks: Update column letters (like "A" for names, "T" for the BreakList target value, "B" for Sheet1's destination column) to match your actual spreadsheet layout.
• Skipping Display Rows: The code skips rows that are blank in the name column or look like dates. If your time period rows have a specific identifier (e.g., a header like "Time Block"), modify the If condition to target those rows specifically.
• Duplicate Names: The code retains the first occurrence of a duplicate name in BreakList. To keep the last occurrence instead, remove the If Not nameLookup.Exists(currentName) Then check and just assign nameLookup(currentName) = tColumnValue directly.
• Performance: Using a dictionary makes this macro run much faster than nested loops, especially with large datasets.

1. Press Alt + F11 to open the VBA Editor.
2. Right-click your workbook in the Project Explorer > Insert > Module.
3. Paste the code into the new module.
4. Adjust the column/worksheet references to fit your file.
5. Press F5 to run the macro, or assign it to a button in Excel for one-click access.

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