优化无需使用VLOOKUP的VBA跨表查找代码需求

问题概述 #

• 源表：2列（需扩展至200列）共200行数据，第1列为匹配键，其余列为待提取的数据
• 目标表：300行数据，第1列为匹配键，需将源表对应匹配键的列数据填充到目标表中
• 限制：禁止使用VLOOKUP函数，原双重循环代码因多列复制耗时过长，需优化

原代码（性能瓶颈） #

For i = 1 To lastrow of Target tab
    For j = 1 To lastrow of source tab
        If target.cells(i,1) = source.cells(j,1) Then
            target.cells(i,2)= source.cells(j,2)
            Exit For
        End If
    Next j
Next i

原代码采用嵌套循环，时间复杂度为O(目标表行数×源表行数)，且逐单元格读写工作表，交互次数多，多列复制时性能急剧下降。

优化方案：使用字典（Scripting.Dictionary） #

核心逻辑 #

1. 先遍历源表，将**匹配键（第1列）**作为字典的键，对应行的待复制数据（从第2列到最后一列）作为字典的值存入
2. 再遍历目标表，通过匹配键直接从字典中取值，一次性写入对应行，避免嵌套循环和频繁的工作表交互

优化后代码（支持多列复制） #

Sub MatchAndFillData()
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim lastRowSource As Long, lastRowTarget As Long, lastColSource As Long
    Dim dict As Object
    Dim i As Long
    Dim matchKey As Variant
    
    ' 替换为实际工作表名称
    Set wsSource = ThisWorkbook.Worksheets("源表")
    Set wsTarget = ThisWorkbook.Worksheets("目标表")
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' 获取源表的有效行、列数
    lastRowSource = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
    lastColSource = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column
    
    ' 批量存入字典：键为匹配值，值为对应行的待复制数据数组
    For i = 1 To lastRowSource
        matchKey = wsSource.Cells(i, 1).Value
        If Not dict.Exists(matchKey) Then
            dict(matchKey) = wsSource.Range(wsSource.Cells(i, 2), wsSource.Cells(i, lastColSource)).Value
        End If
    Next i
    
    ' 获取目标表有效行数
    lastRowTarget = wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).Row
    
    ' 批量填充目标表
    For i = 1 To lastRowTarget
        matchKey = wsTarget.Cells(i, 1).Value
        If dict.Exists(matchKey) Then
            wsTarget.Range(wsTarget.Cells(i, 2), wsTarget.Cells(i, lastColSource)).Value = dict(matchKey)
        End If
    Next i
    
    ' 释放对象
    Set dict = Nothing
    Set wsSource = Nothing
    Set wsTarget = Nothing
End Sub

优化效果说明 #

• 时间复杂度优化：从O(300×200)=60000次操作降到O(200+300)=500次操作，多列场景下性能提升极其明显
• 减少工作表交互：通过数组批量读写，避免逐单元格操作（VBA与Excel工作表的交互是性能瓶颈核心）
• 扩展性强：支持任意多列（包括200列）的复制，无需修改核心逻辑

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