Excel VBA：利用多维数组实现Completion%M2到M1的匹配填充

需求说明 #

• 表格列：Project #、Phase #、$$$$、Completion % M1、Completion % M2，新增UID列作为唯一匹配标识
• 每月更新数据时（可能增删项目/阶段），需将旧数据中Completion % M2的值迁移到对应匹配项的Completion % M1列
• 通过多维数组存储旧数据，匹配新数据的UID后完成填充

完善后的VBA代码 #

Sub UpdateCompletionData()
    Dim ws As Worksheet
    Dim lastRowOld As Long, lastRowNew As Long
    Dim strArray0 As Variant ' 存储旧数据（含UID和原Completion % M2）
    Dim newData As Variant ' 存储新数据（含UID）
    Dim uidDict As Object
    Dim i As Long
    
    ' 指定目标工作表，按需修改表名
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set uidDict = CreateObject("Scripting.Dictionary")
    
    ' 读取旧数据到数组（假设UID在第1列，Completion % M2在第5列）
    lastRowOld = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    strArray0 = ws.Range("A2:E" & lastRowOld).Value
    
    ' 把旧数据的UID与对应Completion % M2存入字典，加快匹配速度
    For i = LBound(strArray0, 1) To UBound(strArray0, 1)
        If strArray0(i, 1) <> "" Then
            uidDict(strArray0(i, 1)) = strArray0(i, 5)
        End If
    Next i
    
    ' 读取新数据到数组（新数据从第2行开始，UID在第1列）
    lastRowNew = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    newData = ws.Range("A2:E" & lastRowNew).Value
    
    ' 遍历新数据，匹配UID后填充Completion % M1（第4列）
    For i = LBound(newData, 1) To UBound(newData, 1)
        If uidDict.Exists(newData(i, 1)) Then
            newData(i, 4) = uidDict(newData(i, 1))
        Else
            ' 无匹配的新项目/阶段，可自定义默认值，此处留空
            newData(i, 4) = ""
        End If
    Next i
    
    ' 将更新后的数据写回工作表
    ws.Range("A2:E" & lastRowNew).Value = newData
    
    ' 释放对象
    Set uidDict = Nothing
    Set ws = Nothing
    
    MsgBox "数据更新完成！", vbInformation
End Sub

关键逻辑说明 #

• 字典匹配：用Scripting.Dictionary存储UID与对应值，比数组嵌套循环效率更高，适配大数据量场景
• 数组读写：一次性读取/写入数据，减少与工作表的交互次数，提升运行效率
• 空值校验：添加UID非空判断，避免空值导致的匹配异常
• 扩展性：无匹配的新项目/阶段可按需设置默认值（如0或特定文本）

内容的提问来源于stack exchange，提问作者C. Lucero