Excel VBA实现仅空单元格填充、更新已有值的公式向下填充

核心需求对应实现逻辑 #

针对两个工作表的ID匹配场景，需要同时满足以下规则：

• 目标表AssetName SheetE列为空、且B列有有效名称时，自动匹配AMP Sheet中对应Name的AMP ID填入
• E列已有ID的单元格，自动同步AMP Sheet中最新的匹配结果，不保留过期旧值
• 无匹配结果时单元格留空，不返回错误值

原有代码的问题 #

你之前写的逐行遍历版本存在3个会导致逻辑失效的问题：

1. 公式中匹配单元格固定写死为B1，逐行写入时不会随行号调整，所有行都会匹配第1行的名称
2. 循环终止条件设置错误，遇到第一个非空E列单元格就会停止遍历，后续所有行都会被跳过
3. 没有覆盖已有单元格的逻辑，无法实现旧ID的同步更新
   原AutoFill全量填充方案会覆盖E列所有内容，运行效率低，也无法跳过不需要处理的行。

可直接运行的实现代码 #

Sub SyncAMPID()
    Dim wsAsset As Worksheet
    Dim lastRow As Long, i As Long
    Dim baseFormula As String
    
    ' 绑定工作表对象
    Set wsAsset = ThisWorkbook.Sheets("AssetName Sheet")
    ' 取B列最后一行有效数据的行号，确定遍历范围
    lastRow = wsAsset.Cells(wsAsset.Rows.Count, "B").End(xlUp).Row
    
    ' 定义基础公式，用{row}作为行号占位符
    baseFormula = "=IFERROR(INDEX(OFFSET('AMP Sheet'!$A:$A,,MATCH(""ID"",'AMP Sheet'!$1:$1,0)-1)," & _
                  "MATCH(B{row},OFFSET('AMP Sheet'!$A:$A,,MATCH(""Name"",'AMP Sheet'!$1:$1,0)-1),0)),"""")"
    
    ' 关闭屏幕更新提升大数量下的运行速度
    Application.ScreenUpdating = False
    ' 从第2行开始遍历（默认第1行为表头）
    For i = 2 To lastRow
        ' 仅处理B列有名称的行
        If Trim(wsAsset.Cells(i, "B").Value) <> "" Then
            ' 替换占位符为当前行号，写入公式：空单元格直接填充，有旧值的单元格自动重算更新
            wsAsset.Cells(i, "E").Formula = Replace(baseFormula, "{row}", i)
            ' 如果不需要保留公式、只需要静态ID值，取消下面一行的注释即可
            ' wsAsset.Cells(i, "E").Value = wsAsset.Cells(i, "E").Value
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

逻辑说明 #

• 遍历范围以B列有效数据为边界，不会出现漏行、提前终止的问题
• 逐行替换公式行号，保证每一行都匹配当前行B列的名称，不会出现匹配错位
• 仅对B列有值的行写入公式，既填充了空单元格，也覆盖了旧ID实现同步更新，无匹配结果时自动留空
• 加入屏幕更新开关，数据量超过千行时运行速度明显快于全量AutoFill
• 可选静态值转换，写入后直接将公式转为固定值，避免后续数据源变动触发自动重算

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