需求：编写VBA代码实现跨工作簿带CONCAT的VLOOKUP至末行

需求回顾 #

• 弹出选择框获取.xlsb格式的输入文件路径
• 在输入文件的CL列批量应用公式：=VLOOKUP(CONCAT(U2,V2,AJ2,AK2,AL2),[Code file.xlsx]Sheet99'!$D:$Q,14,0)，直到D列最后一行数据
• 操作完成后弹出"Done"提示框

修正后的代码 #

Sub ProcessData()
    Dim inputFile As String
    Dim wb As Workbook
    Dim wsData As Worksheet
    Dim lastRow As Long
    Dim formulaText As String
    
    ' 获取输入文件路径
    inputFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsb), *.xls; *.xlsx; *.xlsb", Title:="选择输入文件")
    
    ' 用户取消选择则退出
    If inputFile = "False" Then Exit Sub
    
    ' 打开输入文件
    Set wb = Workbooks.Open(inputFile)
    Set wsData = wb.Sheets(1) ' 假设数据在第一个工作表
    
    ' 获取D列最后一行数据行号
    lastRow = wsData.Cells(wsData.Rows.Count, "D").End(xlUp).Row
    
    ' 构建公式：使用代码文件完整路径引用Sheet99，避免路径失效
    formulaText = "=VLOOKUP(CONCAT(U@,V@,AJ@,AK@,AL@),'[" & ThisWorkbook.FullName & "]Sheet99'!$D:$Q,14,0)"
    
    ' 给CL列批量写入公式（从第2行到最后一行）
    wsData.Range("CL2:CL" & lastRow).Formula = formulaText
    
    ' 弹出完成提示
    MsgBox "Done"
End Sub

关键修改说明 #

• 贴合需求：移除原代码中先拼接CL列值再VLOOKUP的逻辑，直接批量写入需求指定的公式，无需额外中间步骤
• 路径可靠性：用ThisWorkbook.FullName生成代码文件的完整路径，写入公式后，即使代码文件重命名或移动，公式仍能正确指向Sheet99
• 效率优化：批量给整列赋值公式，替代逐行循环，大幅提升处理速度
• 公式适配：使用@结构化引用，确保批量赋值时公式自动对应每一行的单元格

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