Excel导入时VBA运行时错误'91'的解决需求

问题根源 #

运行时错误'91'是因为Range.Find方法找不到匹配值时返回Nothing，此时直接访问rng.Row会触发对象变量未设置的错误。另外原代码未在每次循环初始化变量，导致找不到数据时会保留上一行的变量值，从而出现错误复制的情况。

修改后的代码 #

Option Explicit
Sub IMPORT()
    'DECLARE VARIABLES
    Dim job As String
    Dim engrdue, dftdue, engrout, dftout As String
    Dim engr, drafter, tosales, PM As String
    Dim loaddate, shopdate, detailer, rapp As String
    Dim atype, edwgs, AB As String
    Dim i As Integer
    Dim rng As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Dim wsTarget As Worksheet
    
    ' 简化目标工作表引用，减少重复代码
    Set wsTarget = Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE")
    
    'CLEAR CELLS - 合并连续范围，简化代码
    wsTarget.Range("B2:F40,H2:J40,K2:N40,P2:Q40").ClearContents
    
    'OPEN WORKBOOKS - 添加容错，避免文件不存在直接崩溃
    On Error Resume Next
    Workbooks.Open ("S:\Scheduling\ASI Master Schedule\ASI Master Schedule.xlsx")
    Workbooks.Open ("S:\Drafting_Detailing\CCCCC_\CCCCC99.xls")
    Workbooks.Open ("S:\Scheduling\ASI Master Schedule\DAILYTONNAGELOG.xls")
    On Error GoTo 0
    
    'SET VARIABLES
    For i = 2 To 40
        ' 初始化所有变量为"No data"，确保缺失时显示统一内容
        engrdue = "No data"
        dftdue = "No data"
        engrout = "No data"
        dftout = "No data"
        engr = "No data"
        drafter = "No data"
        tosales = "No data"
        PM = "No data"
        loaddate = "No data"
        shopdate = "No data"
        detailer = "No data"
        rapp = "No data"
        atype = "No data"
        edwgs = "No data"
        AB = "No data"
        
        job = wsTarget.Cells(i, "A").Value
        ' 跳过空job行
        If job = "" Then GoTo NextRow
        
        ' 查找ASI Master Schedule中的job，精确匹配
        Set rng = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Range("R:R").Find(job, LookIn:=xlValues, LookAt:=xlWhole)
        If Not rng Is Nothing Then
            tosales = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CH").Value
            PM = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "BH").Value
            loaddate = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "L").Value
            shopdate = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "DD").Value
            detailer = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "DB").Value
            rapp = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CW").Value
            engrdue = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CJ").Value
            engrout = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CK").Value
            engr = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CL").Value
            dftdue = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CN").Value
            dftout = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CO").Value
            drafter = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CP").Value
        End If
        
        ' 查找CCCCC99.xls中的job，精确匹配
        Set rng2 = Workbooks("CCCCC99.xls").Worksheets("JOB INFO").Range("A:A").Find(job, LookIn:=xlValues, LookAt:=xlWhole)
        If Not rng2 Is Nothing Then
            AB = Workbooks("CCCCC99.xls").Worksheets("JOB INFO").Cells(rng2.Row, "X").Value
            atype = Workbooks("CCCCC99.xls").Worksheets("JOB INFO").Cells(rng2.Row, "AC").Value
            edwgs = Workbooks("CCCCC99.xls").Worksheets("JOB INFO").Cells(rng2.Row, "Y").Value
        End If
        
        ' 赋值到目标工作表
        wsTarget.Cells(i, "B") = tosales
        wsTarget.Cells(i, "Q") = PM
        wsTarget.Cells(i, "P") = loaddate
        wsTarget.Cells(i, "O") = shopdate
        wsTarget.Cells(i, "N") = detailer
        wsTarget.Cells(i, "L") = rapp
        wsTarget.Cells(i, "M") = atype
        wsTarget.Cells(i, "C") = engrdue
        wsTarget.Cells(i, "D") = engrout
        wsTarget.Cells(i, "E") = engr
        wsTarget.Cells(i, "F") = dftdue
        wsTarget.Cells(i, "H") = AB
        wsTarget.Cells(i, "I") = dftout
        wsTarget.Cells(i, "J") = drafter
        wsTarget.Cells(i, "K") = edwgs
        
NextRow:
    Next i

End Sub

关键修改说明 #

• 变量初始化：每次循环开始时将所有字段变量设为"No data"，确保找不到数据时不会保留上一行的值
• 空job跳过：如果目标工作表A列当前行是空，直接跳过该行处理
• Find结果判断：对每个Range.Find的结果用If Not rng Is Nothing Then判断，只有找到匹配值时才赋值，彻底避免错误91
• 简化代码：用变量wsTarget指代目标工作表，减少重复的长路径引用；合并连续的单元格清除范围，让代码更简洁
• 文件打开容错：添加简单的错误处理，避免文件不存在时直接崩溃

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