VBA中引用动态表格自动填充公式（R1C1/A1样式）的问题

核心思路 #

要在公式中引用VBA变量定义的动态范围，关键是将VBA的Range对象转换为Excel公式能识别的地址字符串，可通过Range.Address()方法实现，同时根据需求选择A1或R1C1样式的地址格式。

修正原代码顺序 #

原代码中Set wsbin的位置错误，需先定义工作表对象，再获取最后一行，否则会触发错误：

Set wsbin = Inputwb.Worksheets("Raport_POS_BIN")
LastRowBin = wsbin.Cells(Rows.Count, "B").End(xlUp).Row
colcopydoi = Application.Match(Search_doi, wsbin.Range("4:4"), 0)
newtgtlastrow = shttgt.Cells(Rows.Count, "A").End(xlUp).Row

方法一：A1样式公式（.Formula） #

直接构建包含动态范围地址的A1样式公式，批量写入目标区域（无需AutoFill，效率更高）：

' 构建完整查找范围：B列（查找值所在列）到目标列colcopydoi，行范围5到LastRowBin
Dim lookupRange As Range
Set lookupRange = wsbin.Range(wsbin.Cells(5, 2), wsbin.Cells(LastRowBin, colcopydoi))

' 获取带外部工作表引用的A1地址（格式示例：[Input.xlsx]Raport_POS_BIN!$B$5:$D$1000）
Dim lookupAddr As String
lookupAddr = lookupRange.Address(External:=True)

' 计算VLOOKUP返回列的索引（在lookupRange中的列位置）
Dim colIndex As Long
colIndex = colcopydoi - 2 + 1 ' 起始列是B（第2列），索引为目标列与起始列的差值+1

' 批量写入公式到D2到D最后一行
With shttgt.Range("D2:D" & newtgtlastrow)
    .Formula = "=IFNA(VLOOKUP(A2," & lookupAddr & "," & colIndex & ",FALSE),0)"
End With

方法二：R1C1样式公式（.FormulaR1C1） #

R1C1样式的相对引用更适合批量操作，无需担心单元格位置变化：

' 构建动态范围的R1C1格式地址（带外部引用）
Dim lookupAddrR1C1 As String
lookupAddrR1C1 = wsbin.Range(wsbin.Cells(5, 2), wsbin.Cells(LastRowBin, colcopydoi)).Address(ReferenceStyle:=xlR1C1, External:=True)

' 写入R1C1公式：RC[-3]表示当前行的左3列（即A列，目标列是D列）
With shttgt.Range("D2:D" & newtgtlastrow)
    .FormulaR1C1 = "=IFNA(VLOOKUP(RC[-3]," & lookupAddrR1C1 & "," & (colcopydoi - 2 + 1) & ",FALSE),0)"
End With

注意事项 #

• 若Application.Match未找到Search_doi，colcopydoi会返回错误值，建议添加错误处理：

  If IsError(colcopydoi) Then
      MsgBox "未找到目标列：" & Search_doi
      Exit Sub
  End If
  

• VLOOKUP要求查找值必须在查找范围的第一列，因此确保构建的lookupRange第一列是查找值所在列（示例中为B列）。

内容的提问来源于stack exchange，提问作者Dan Andrei Sica