Excel更新数据连接新增数据时行处理标记错位问题求助

现有绑定Smartsheet数据连接的Excel表格，同步获取的数据供宏程序调用：宏会为每一条完成处理的行添加专属标记，避免后续重复执行处理流程。当前故障为：每次刷新数据连接同步新数据时，新增数据可正常写入，但之前通过宏添加的处理标记无法和对应数据行绑定，出现位置偏移。
各阶段表格状态：

• 运行宏前的表格状态：
  
• 运行宏后的表格状态：
  
• 更新数据连接后的表格状态：
  

当前宏添加标记的代码如下：

db.DataBodyRange(i, 20).Value2 = 1    
db.DataBodyRange(i, 21).Value2 = 1

故障核心是用行物理位置作为行匹配依据，完全没有绑定行唯一标识：

1. 代码中通过DataBodyRange(i, 列号)的方式写入标记，本质是按行的序号位置存标记，和行本身的业务数据没有绑定关系。
2. Smartsheet数据连接刷新时，会按照接口返回的行顺序重写数据区域，只要Smartsheet端出现新增行、删除行、行排序调整、行位置移动，返回的行序就会变化，原来存在第i行的标记自然会对应到错误的数据行，出现偏移。
3. 如果标记列属于绑定外部连接的ListObject（即代码中的db对象）的表结构范围内，刷新时Excel不会自动做行维度的标记匹配，只会直接覆盖数据区内容，进一步加剧错位问题。

按稳定性从高到低可选以下方案：

方案1（推荐，永久解决）：独立存储标记，靠Smartsheet唯一行ID匹配 #

Smartsheet每一行都有全局唯一、终身不变的系统行ID，完全不会随行的位置、内容修改变化，用这个ID做匹配依据可以100%避免错位：

• 调整Smartsheet同步配置，将系统自带的「行ID」字段加入同步列，固定同步到Excel表中作为每一行的唯一标识。
• 新建独立工作表（例如命名为Processed_Log），仅存储3列内容：Smartsheet_RowID、Mark1、Mark2，专门用来记录已处理行的标记，不要把标记存在同步表的ListObject范围内。
• 调整宏逻辑：遍历数据行时，先读取当前行的Smartsheet行ID，到Processed_Log表中查询是否存在对应记录，存在则跳过处理，不存在则执行对应处理流程，处理完成后将该行ID和标记值写入Processed_Log表。
• 该方案完全和行位置解耦，无论怎么刷新数据、调整行顺序、增删行，都不会出现标记错位。

方案2（无需新建表）：刷新前缓存标记，刷新后按唯一ID回写 #

如果要把标记保留在当前同步表的20、21列，需要在每次刷新前先把标记按唯一ID缓存到内存，刷新完成后再匹配回写，参考代码如下：

Sub RefreshDataAndKeepMarks()
    Dim db As ListObject
    Dim markDict As Object
    Dim i As Long, currentRowID As String
    ' 按实际情况修改表名、工作表名、连接名
    Set db = ThisWorkbook.Worksheets("数据表").ListObjects("同步表名")
    Set markDict = CreateObject("Scripting.Dictionary")
    
    ' 刷新前缓存所有已有标记：假设第1列为Smartsheet行ID/唯一业务标识
    For i = 1 To db.ListRows.Count
        currentRowID = CStr(db.DataBodyRange(i, 1).Value2)
        If currentRowID <> "" Then
            markDict(currentRowID) = Array( _
                db.DataBodyRange(i, 20).Value2, _
                db.DataBodyRange(i, 21).Value2 _
            )
        End If
    Next
    
    ' 执行Smartsheet连接刷新
    ThisWorkbook.Connections("Smartsheet同步连接").Refresh
    
    ' 刷新完成后按唯一ID回写标记
    For i = 1 To db.ListRows.Count
        currentRowID = CStr(db.DataBodyRange(i, 1).Value2)
        If markDict.Exists(currentRowID) Then
            ' 已处理过的行写回原有标记
            db.DataBodyRange(i, 20).Value2 = markDict(currentRowID)(0)
            db.DataBodyRange(i, 21).Value2 = markDict(currentRowID)(1)
        Else
            ' 新增行默认清空标记
            db.DataBodyRange(i, 20).Value2 = Empty
            db.DataBodyRange(i, 21).Value2 = Empty
        End If
    Next i
End Sub

注意：如果没有同步Smartsheet行ID，也可以用每行不会重复的业务字段（例如订单号、任务编号）作为唯一键，只要保证该字段值不会重复、不会修改即可。

方案3（临时方案，不推荐）：标记列移出同步表范围 #

将存标记的20、21列移出ListObject的表范围（即放在同步表最后一列的右侧，不属于表结构的区域），通过XLOOKUP等函数按唯一ID匹配对应标记值。该方案实现简单，但表行数变动时容易出现公式填充错位，仅适合临时应急使用。

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