VBA中Range.Find方法失效，运行时错误'91'求助

Hey there, let's break down why you're hitting that Run-time error '91' (Object variable or With block variable not set) when adjusting your dropdown selections. The core issue is your code doesn't verify if the Find method actually locates a matching value—when it can't find a match, it returns Nothing, and trying to access .Row on a Nothing object triggers the error. Plus, relying on Activate and Select makes your code fragile and prone to unexpected crashes. Let's fix this step by step.

Why the Error Occurs #

When you switch your DC/Mode/Shift dropdowns to a combination that doesn't exist in your "Daily Route Master Data" sheet, the Find method fails to locate the value. Instead of returning a valid Range object, it gives back Nothing. Your code then attempts to execute lines like DC = FindRow.Row, which crashes because FindRow isn't pointing to any actual cell.

Modified Code with Critical Fixes #

First, always start your VBA modules with Option Explicit—this forces you to declare all variables, catching typos and undeclared variable issues early. Here's the revised code with error handling, no reliance on Activate/Select, and proper validation:

Option Explicit

Sub DailyRouteInput_Button8_Click()
    Dim wsInput As Worksheet
    Dim wsMaster As Worksheet
    Dim DC As String
    Dim Mode As String
    Dim Shift As Integer
    Dim FindRowDC As Range
    Dim FindRowMode As Range
    Dim FindRowShift As Range
    Dim WeekCheck As Variant
    Dim MonthCheck As Variant
    
    ' Directly reference worksheets (no need to activate them!)
    Set wsInput = ThisWorkbook.Worksheets("Daily Route Input")
    Set wsMaster = ThisWorkbook.Worksheets("Daily Route Master Data")
    
    ' Pull values directly from input cells (no selecting required)
    DC = wsInput.Range("U1").Value
    Mode = wsInput.Range("V1").Value ' Assumes this is the cell adjacent to U1
    Shift = wsInput.Range("C5").Value
    
    ' Step 1: Locate DC in Column A of the master sheet
    Set FindRowDC = wsMaster.Range("A:A").Find(What:=DC, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    If FindRowDC Is Nothing Then
        MsgBox "DC '" & DC & "' not found in master data!", vbExclamation
        Exit Sub
    End If
    
    ' Step 2: Locate Mode in Column B (adjust range if you need to search only DC-associated rows)
    Set FindRowMode = wsMaster.Range("B:B").Find(What:=Mode, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    If FindRowMode Is Nothing Then
        MsgBox "Mode '" & Mode & "' not found in master data!", vbExclamation
        Exit Sub
    End If
    
    ' Step 3: Locate Shift in Column C
    Set FindRowShift = wsMaster.Range("C:C").Find(What:=Shift, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    If FindRowShift Is Nothing Then
        MsgBox "Shift '" & Shift & "' not found in master data!", vbExclamation
        Exit Sub
    End If
    
    ' Retrieve WeekCheck and MonthCheck without selecting cells
    WeekCheck = wsMaster.Cells(FindRowShift.Row, "D").Value ' Column D is 1 column right of C
    MonthCheck = wsMaster.Cells(FindRowShift.Row, "E").Value ' Column E is 2 columns right of C
    
    ' Optional: Add code here to use WeekCheck and MonthCheck values
    ' MsgBox "Week Check: " & WeekCheck & vbNewLine & "Month Check: " & MonthCheck
End Sub

Key Improvements #

• No more Activate/Select: Directly referencing worksheets and ranges makes the code faster, more reliable, and immune to errors from accidentally selected cells.
• Mandatory Find result checks: Every Find operation is followed by a check for Nothing, with a user-friendly message to explain what went wrong.
• Explicit variable declarations: Option Explicit ensures all variables are declared with their correct data types (no more implicit variant types).
• Clear range references: Using full column ranges (like "A:A") avoids fragile dynamic range logic that might break if your data structure changes.

Customization Tip #

If you need to search for Mode only within rows associated with the found DC (not the entire Column B), adjust the search range like this:

' Search Column B starting from the DC row down to the last row of data
Dim lastRow As Long
lastRow = wsMaster.Cells(wsMaster.Rows.Count, "B").End(xlUp).Row
Set FindRowMode = wsMaster.Range("B" & FindRowDC.Row & ":B" & lastRow).Find(What:=Mode, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

Apply the same logic if you need to restrict the Shift search to rows linked to the found Mode.

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