VBA运行时错误91：对象变量或With块变量未设置问题求助

Hey there, let's break down exactly why you're hitting that frustrating error and fix it up.

The root cause here is straightforward: when the Range.Find method can't locate your DatetoRun value in column A, it returns Nothing (a null object reference). Your code tries to access found.Row right away without checking if found actually exists—this triggers the "Object variable or With block variable not set" error 91.

Here's how to resolve this, with a polished, robust version of your code:

Full Corrected Code #

' Always use Option Explicit at the top of your module to catch undeclared variables
Option Explicit

Sub RunDateLookup()
    Dim DatetoRun As Variant
    Dim dateStart As String
    Dim found As Range
    Dim iclearrow As Long

    DatetoRun = InputBox("How far back do you want to run the MI for?", "Date backing", Format(Now(), "dd/mm/yyyy"))

    ' Date validation for DatetoRun
    If IsDate(DatetoRun) Then
        DatetoRun = Format(CDate(DatetoRun), "dd/mm/yyyy")
        dateStart = DatetoRun
        
        ' Attempt to find the date in column A (with explicit search settings for reliability)
        Set found = Range("A:A").Find( _
            What:=DatetoRun, _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False _
        )
        
        ' Critical check: only access found.Row if we actually found a match
        If Not found Is Nothing Then
            iclearrow = found.Row
            ' Add your follow-up code here, e.g.:
            ' MsgBox "Target date found at row: " & iclearrow
        Else
            MsgBox "The date " & DatetoRun & " wasn't found in column A.", vbExclamation
        End If
    Else
        ' Handles invalid dates AND user clicking "Cancel" on the input box
        MsgBox "Please enter a valid date in dd/mm/yyyy format.", vbExclamation
    End If
End Sub

Key Fixes & Improvements #

• Added Nothing check: The If Not found Is Nothing block ensures we only try to access found.Row when a valid match exists. No more null reference errors!
• Explicit variable declarations: I added Dim statements for all variables (paired with Option Explicit at the top, this prevents typos and undeclared variable bugs).
• Reliable Find parameters: I expanded the Find method with explicit settings to avoid inheriting leftover search preferences from prior Excel actions (like search direction or case sensitivity).
• Clearer user feedback: Now users get specific messages if their date is invalid or not found, instead of a confusing runtime error.
• Handled cancel action: If the user clicks "Cancel" on the input box, DatetoRun becomes an empty string—our IsDate check catches this and shows the appropriate message.

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