使用Worksheet Function VLookup的VBA宏无返回值问题排查

错误原因分析 #

• 变量fgtype的误用：你将fgtype定义为单个Variant变量，却试图用数组索引fgtype(i, 1)赋值，逻辑完全错误。VLookup匹配成功时，fgtype会被赋值为具体的匹配内容而非数组；匹配失败时触发的错误被On Error Resume Next忽略，导致fgtype未被正确赋值，最终写入单元格的是无效内容，呈现空白。
• 错误处理后的逻辑混乱：无论VLookup是否成功，你都不需要修改fgtype的值，只需直接返回"valid"或"invalid"即可，不需要依赖fgtype的内容。

修正后的代码 #

Sub fg_type()
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim ref_wb As Workbook
    Dim ref_ws As Worksheet
    Dim lastRow As Long
    Dim ref_lastRow As Long

    Dim lookup_val As Variant
    Dim table_arr As Variant
    Dim result As String ' 改用明确的字符串变量存储结果

    Set wb = Workbooks("un_orders.xlsm")
    Set ws = wb.Worksheets("uo")
    Set ref_wb = Workbooks("target.xlsx")
    Set ref_ws = ref_wb.Worksheets("Document")

    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    ref_lastRow = ref_ws.Cells(ref_ws.Rows.Count, "G").End(xlUp).Row

    table_arr = ref_ws.Range("G3:G" & ref_lastRow)

    For i = 2 To lastRow
        lookup_val = ws.Range("C" & i).Value
        On Error Resume Next
        Err.Clear
        ' 仅用VLookup判断是否存在匹配，无需存储结果
        Application.WorksheetFunction.VLookup(lookup_val, table_arr, 1, False)
        If Err.Number = 0 Then
            result = "valid"
        Else
            result = "invalid"
        End If
        On Error GoTo 0 ' 关闭错误捕获，避免影响后续代码
        ws.Range("A" & i).Value = result
    Next i

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

额外优化建议 #

• 推荐使用Application.VLookup替代WorksheetFunction.VLookup，无需依赖错误捕获，直接通过IsError判断结果，逻辑更清晰：

' 替换循环内的错误处理代码
result = "invalid"
If Not IsError(Application.VLookup(lookup_val, table_arr, 1, False)) Then
    result = "valid"
End If
ws.Range("A" & i).Value = result

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