VBA中如何替换Offset实现XLOOKUP的绝对引用查找值？

核心实现思路 #

• 自动定位Results表的最新空白列，作为每两周新增的结果列
• 根据Results表A列的实际数据行数，动态确定目标区域范围，替代固定的B3:B50
• 批量写入相对引用的XLOOKUP公式，无需依赖Offset定位lookup_value

完整VBA代码 #

Sub ImportVoteResults()
    Dim wsData As Worksheet, wsResults As Worksheet
    Dim lastCol As Long, lastRow As Long
    Dim targetRange As Range
    
    ' 绑定工作表对象，避免硬编码表名出错
    Set wsData = ThisWorkbook.Worksheets("Data")
    Set wsResults = ThisWorkbook.Worksheets("Results")
    
    ' 找到Results表最后一列的下一列（作为新增结果列）
    lastCol = wsResults.Cells(1, wsResults.Columns.Count).End(xlToLeft).Column + 1
    
    ' 动态获取Results表A列有数据的最后一行，确定目标区域的行范围
    lastRow = wsResults.Cells(wsResults.Rows.Count, "A").End(xlUp).Row
    
    ' 定义目标区域：从第3行到有效数据行，对应新增列
    Set targetRange = wsResults.Range(wsResults.Cells(3, lastCol), wsResults.Cells(lastRow, lastCol))
    
    ' 批量写入XLOOKUP公式，自动适配每行的A列单元格
    targetRange.Formula = "=XLOOKUP(A3&""*"",Data!$D$2:$D$20,Data!$F$2:$F$20,""F"",2)"
    
    ' 可选：给新增列添加日期标题，方便识别
    wsResults.Cells(2, lastCol).Value = "投票结果_" & Format(Date, "YYYY-MM-DD")
End Sub

关键细节说明 #

1. 动态新增列定位
   lastCol = wsResults.Cells(1, wsResults.Columns.Count).End(xlToLeft).Column + 1
   从工作表最后一列向左查找有内容的列，加1即为空白新列，无需手动指定列号，自动适配每两周新增的需求。

2. 动态行范围适配
   lastRow = wsResults.Cells(wsResults.Rows.Count, "A").End(xlUp).Row
   从A列底部向上查找有效数据行，自动覆盖所有用户输入的行，替代固定的B3:B50范围。

3. 公式相对引用自动适配
   直接使用A3&""*""作为lookup_value，批量写入时Excel会自动将公式中的A3调整为对应行的A列单元格（如第4行公式变为A4&"*"），完全不需要绝对引用或Offset辅助定位。

4. 扩展优化建议
   如果Data表的D/F列数据会持续新增，可以把公式中的固定范围Data!$D$2:$D$20改成动态范围：

   Dim dataLastRow As Long
   dataLastRow = wsData.Cells(wsData.Rows.Count, "D").End(xlUp).Row
   targetRange.Formula = "=XLOOKUP(A3&""*"",Data!$D$2:Data!$D$" & dataLastRow & ",Data!$F$2:Data!$F$" & dataLastRow & ",""F"",2)"
   

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