VBA中.Offset场景下使用列名而非列号引用列的实现方法

核心解决思路：先通过工作簿定义名称拿到对应列的列索引，彻底替换硬编码列号，同时放弃固定偏移的Offset写法，直接通过定义名称定位ID列取值，完全不受列位置调整影响。

改造后完整代码 #

Sub AMPTabid()
    Dim wsAN As Worksheet, wsAMP As Worksheet
    Dim colAN_AssetName As Long, colAMP_Name As Long, colAMP_ID As Long, colAN_AMPID As Long
    Dim LastRow As Long, i As Long
    Dim rFind As Range
    
    ' 绑定目标工作表
    Set wsAN = ThisWorkbook.Worksheets("AssetName Sheet")
    Set wsAMP = ThisWorkbook.Worksheets("AMP Sheet")
    
    ' 全程通过已定义名称获取列索引，无任何硬编码列号
    colAN_AssetName = wsAN.Range("GeneratedAssetName").Column
    colAMP_Name = wsAMP.Range("Name").Column
    colAMP_ID = wsAMP.Range("ID").Column
    ' 若后续给存储AMP ID的E列也添加定义名称（比如命名为MatchedAMPID），可直接替换为wsAN.Range("MatchedAMPID").Column
    colAN_AMPID = 5
    
    ' 取资产名称列最后一行有效数据行号
    LastRow = wsAN.Cells(wsAN.Rows.Count, colAN_AssetName).End(xlUp).Row
    
    ' 逐行模糊匹配
    For i = 2 To LastRow
        ' 默认写入未找到标识，匹配成功后覆盖
        wsAN.Cells(i, colAN_AMPID).Value = "Not Found"
        
        ' Find参数写全，避免继承用户上次手动查找的配置导致匹配异常
        Set rFind = wsAMP.Columns(colAMP_Name).Find( _
            What:=wsAN.Cells(i, colAN_AssetName).Value, _
            LookIn:=xlValues, _
            LookAt:=xlPart, _
            MatchCase:=True, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext _
        )
        
        If Not rFind Is Nothing Then
            ' 直接通过ID列索引取值，无需计算列偏移量，列位置调整也不影响逻辑
            wsAN.Cells(i, colAN_AMPID).Value = wsAMP.Cells(rFind.Row, colAMP_ID).Value
        End If
    Next i
End Sub

关键改造点说明 #

• 移除所有硬编码列号：所有用到的列位置都通过预先定义的名称读取.Column属性获取列索引，后续调整各列前后位置时，只要定义名称不删除，代码不需要做任何修改
• 替换原Offset(0,10)偏移逻辑：匹配到Name列的单元格后，直接通过提前获取的ID列索引定位到同一行的ID值，完全不需要计算两列之间的间隔列数，从根源解决Offset依赖列位置的问题
• 补全原代码缺失逻辑：原代码未处理匹配不到值的场景，改造后默认写入Not Found，和给出的公式逻辑完全对齐
• 补全Find方法全量参数：Excel的Find方法会默认继承上一次用户手动查找的设置，漏写参数会导致匹配结果随机出错，写全所有参数可以保证运行逻辑稳定

注意：如果Name/ID/GeneratedAssetName是工作表级别的定义名称，引用时调整为wsAMP.Names("Name").RefersToRange.Column写法即可，其余逻辑不变。

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