循环添加VLookup函数出现Run-time error 9/1004问题求助

错误原因分析 #

• Run-time error 9（下标越界）：Workbooks("KV_ENVANTER.xlsm")未打开，或文件名/路径拼写错误（注意大小写、文件后缀是否完全匹配）
• Run-time error 1004：
  • VLookup未找到匹配值，WorksheetFunction.VLookup找不到匹配会直接抛出错误
  • 修改单元格时触发递归的Worksheet_Change事件，导致程序冲突
  • 目标工作表sheet3不存在，或区域D2:E9超出有效范围

修复后的代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim V As Range
    Dim cell1 As Range
    Dim isect1 As Range
    Dim lookupWB As Workbook
    Dim lookupWS As Worksheet
    Dim lookupRange As Range
    Dim lookupResult As Variant
    
    ' 禁用事件，防止修改单元格时递归触发Worksheet_Change
    Application.EnableEvents = False
    
    On Error GoTo Cleanup ' 错误捕获，确保事件能恢复
    
    Set V = Me.Range("D4:D29")
    Set isect1 = Intersect(Target, V)
    
    If isect1 Is Nothing Then
        GoTo Cleanup
    End If
    
    ' 检查目标工作簿是否打开
    On Error Resume Next
    Set lookupWB = Workbooks("KV_ENVANTER.xlsm")
    On Error GoTo Cleanup
    If lookupWB Is Nothing Then
        MsgBox "KV_ENVANTER.xlsm 未打开，请先打开该文件", vbExclamation
        GoTo Cleanup
    End If
    
    ' 检查目标工作表是否存在
    On Error Resume Next
    Set lookupWS = lookupWB.Sheets("sheet3")
    On Error GoTo Cleanup
    If lookupWS Is Nothing Then
        MsgBox "KV_ENVANTER.xlsm 中不存在sheet3工作表", vbExclamation
        GoTo Cleanup
    End If
    
    ' 定义查找区域
    Set lookupRange = lookupWS.Range("D2:E9")
    
    For Each cell1 In isect1
        If cell1.Value = "" Then
            cell1.Offset(0, 1).ClearContents
        Else
            ' 用Application.VLookup，找不到匹配时返回错误值而非直接报错
            lookupResult = Application.VLookup(cell1.Value, lookupRange, 2, False)
            If Not IsError(lookupResult) Then
                cell1.Offset(0, 1).Value = lookupResult
            Else
                ' 找不到匹配时的处理，可根据需求修改
                cell1.Offset(0, 1).Value = "无匹配"
            End If
        End If
    Next cell1

Cleanup:
    ' 恢复事件触发
    Application.EnableEvents = True
End Sub

关键改动说明 #

• 禁用事件递归：通过Application.EnableEvents = False避免修改单元格时重复触发事件，防止程序崩溃
• 对象存在性检查：先确认工作簿、工作表是否存在，避免下标越界错误
• 改用Application.VLookup：相比WorksheetFunction.VLookup，它在找不到匹配时返回错误值而非直接抛出1004错误，可通过IsError灵活处理这种情况
• 错误捕获机制：通过On Error GoTo Cleanup确保无论是否报错，事件都能恢复启用，避免后续Excel操作异常

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