Excel VBA实现VLOOKUP批量匹配Portfolio Number及适用性咨询

VLOOKUP适用性判断 #

VLOOKUP完全适用于你的需求：主数据表的Fund Number是查找区域的首列（假设主数据表A列为Fund Number），刚好符合VLOOKUP要求查找值必须位于查找区域首列的规则，通过匹配Fund Number即可返回对应位置第3列的Portfolio Number。

原代码存在的问题 #

1. 范围语法错误：sh.Range("B2" & lr) 写法错误，应改为 sh.Range("B2:B" & lr)，否则无法选中B2到最后一行的连续区域。
2. 数据类型溢出风险：lr 定义为Integer，但Excel最大行数远超Integer的上限（32767），建议改用Long类型避免溢出。
3. 逻辑冲突：你要在工作文件表的B列写入公式，但B列本身是Fund Number的查找值，写入公式会直接覆盖原始数据，这是严重逻辑错误，应将结果放到其他空白列（比如C列）。

修正后的VBA代码 #

Sub VLOOKUP_Formula_Fixed()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("工作文件表") ' 注意工作表名要和实际一致，原代码写的是"Working File"
    
    Dim lr As Long ' 改用Long避免行数溢出
    lr = sh.Range("B" & Application.Rows.Count).End(xlUp).Row ' 获取B列最后一行行号
    
    ' 在C列写入VLOOKUP公式，匹配主数据表的Fund Number，返回Portfolio Number
    sh.Range("C2").Formula = "=VLOOKUP(B2,主数据表!A:C,3,0)"
    
    ' 填充公式到最后一行
    sh.Range("C2:C" & lr).FillDown
    
    ' 将公式转换为数值
    sh.Range("C2:C" & lr).Copy
    sh.Range("C2:C" & lr).PasteSpecial xlPasteValues
    
    Application.CutCopyMode = False
End Sub

补充说明 #

• 如果主数据表的Fund Number不在A列，需要调整公式中的主数据表!A:C区域，确保查找值是该区域的首列。
• 若匹配不到对应值，公式会返回#N/A，可以用IFERROR包裹公式处理错误值，比如：=IFERROR(VLOOKUP(B2,主数据表!A:C,3,0),"无匹配")

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