我来帮你搞定这个困扰——你的核心需求是让VLOOKUP的数据源范围能自动适配每周变化的数据量，同时保证不遗漏任何行。咱们先拆解你原有代码的问题，再给出几个实用的解决方案：

原代码的问题分析 #

你原来的公式里写的是'[EMP.txt]EMP'!$A$2:$C2，这里的$C2是相对引用，当你把公式复制到下面的行时，范围会变成$A$2:$C3、$A$2:$C4……这种逐行扩大的范围可能会导致部分行无法匹配到完整的数据源，进而出现数据遗漏；而固定范围$A$2:$C176虽然能覆盖当前数据，但无法应对每周变化的行数。

解决方案1：动态获取外部文本文件的最后一行（最贴合你的原有逻辑） #

我们可以先打开EMP.txt文件，获取它的实际最后一行行号，再把这个行号拼入VLOOKUP公式，让范围自动适配数据量：

Sub test()
    Dim sourceWB As Workbook
    Dim lastRowSource As Long
    Dim destLastRow As Long
    Dim destSheet As Worksheet
    
    ' 替换成你的目标工作表名称
    Set destSheet = ThisWorkbook.Sheets("Sheet1")
    
    ' 1. 打开外部EMP.txt文件，获取数据源的最后一行
    ' 替换成EMP.txt的实际路径（注意路径里的反斜杠要写对，或者用/）
    Set sourceWB = Workbooks.Open("C:\Data\EMP.txt")
    With sourceWB.Sheets("EMP")
        lastRowSource = .Range("A" & .Rows.Count).End(xlUp).Row
    End With
    sourceWB.Close SaveChanges:=False ' 关闭文件，不保存
    
    ' 2. 获取目标工作表中A列的最后一行（需要匹配的数据行数）
    destLastRow = destSheet.Range("A" & destSheet.Rows.Count).End(xlUp).Row
    
    ' 3. 给整个C列区域批量写入动态范围的VLOOKUP公式
    With destSheet.Range("C4:C" & destLastRow)
        .Formula = "=VLOOKUP(A4, '[EMP.txt]EMP'!$A$2:$C$" & lastRowSource & ", 3, FALSE)"
        ' 可选：把公式转换成值，避免每次打开都重新计算
        '.Value = .Value
    End With
End Sub

这个方法的优势是完全自动化，每次运行都会自动识别最新的数据源行数，不用手动调整范围。

解决方案2：用Excel表格（ListObject）实现自动扩展范围 #

如果把EMP.txt的数据导入Excel后转换成结构化表格，公式就可以用表格的结构化引用，范围会自动随数据增减而扩展：

步骤： #

1. 打开EMP.txt，选中所有数据区域，按下Ctrl+T，勾选「我的表格有标题」，把数据转成表格。
2. 修改VBA代码，使用表格的结构化引用：

Sub testWithTable()
    Dim destLastRow As Long
    Dim destSheet As Worksheet
    
    Set destSheet = ThisWorkbook.Sheets("Sheet1")
    destLastRow = destSheet.Range("A" & destSheet.Rows.Count).End(xlUp).Row
    
    With destSheet.Range("C4:C" & destLastRow)
        ' 这里的Table1替换成你实际的表格名称，[#All]代表整个表格区域
        .Formula = "=VLOOKUP(A4, '[EMP.txt]EMP'!Table1[#All], 3, FALSE)"
        ' 或者更精确地指定列：Table1[[ID列]:[目标列]]（替换成你的列名）
    End With
End Sub

以后每周更新EMP.txt的数据时，只要刷新表格，公式的范围就会自动更新，非常省心。

解决方案3：用INDEX+MATCH替代VLOOKUP（更稳定的动态匹配） #

如果你不想处理行号的问题，可以直接用INDEX+MATCH组合，它支持直接引用整列，不用关心数据源的最后一行：

Sub testWithIndexMatch()
    Dim destLastRow As Long
    Dim destSheet As Worksheet
    
    Set destSheet = ThisWorkbook.Sheets("Sheet1")
    destLastRow = destSheet.Range("A" & destSheet.Rows.Count).End(xlUp).Row
    
    With destSheet.Range("C4:C" & destLastRow)
        .Formula = "=INDEX('[EMP.txt]EMP'!$C:$C, MATCH(A4, '[EMP.txt]EMP'!$A:$A, 0))"
    End With
End Sub

这个方法的逻辑和VLOOKUP一致，但因为直接引用整列，不管数据源新增多少行，都能正常匹配，而且避免了范围设置错误的问题。

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