Excel VBA查找日期触发运行时错误'91'求助

Hey there, let's break down why you're hitting that random Runtime Error '91' and how to fix it for good. The root issue is exactly what you spotted: your Find method is occasionally returning Nothing, even though the date exists in your header range. Let's go through the problems in your code and the fixes step by step.

Why the Error Happens (Even When the Date Exists) #

• Unqualified Range Reference: Inside your With TargetHeader block, you use Range("D119:HF119").Find instead of TargetHeader.Find or explicitly referencing the worksheet. If another sheet is active when the code runs, Range will point to that active sheet instead of "Data Sheet 2"—so it's looking for the date in the wrong place, returning Nothing.
• No Check for Nothing: Before you try to use TodaysDate.Offset(1, 0), you don't verify if TodaysDate actually found something. If Find fails, accessing .Offset on Nothing throws Error 91 immediately.
• Potential Date Format Mismatch: Even if cells look like dates, Excel might store them as text (or vice versa). Using CDate(Date) as the search value might not match the underlying data type in your header range.

Corrected Code with Explanations #

Here's the revised code that addresses all these issues:

Sub Save_Login_Data()
    Dim Source As Range
    Set Source = Worksheets("Data Sheet 1").Range("AV12:AV29")
    
    Dim TargetSheet As Worksheet
    Set TargetSheet = Worksheets("Data Sheet 2")
    Dim TargetHeader As Range
    Set TargetHeader = TargetSheet.Range("D119:HF119")
    
    Dim Today As Date
    Today = Date ' No need for CDate(Date) since Date already returns a Date type
    
    Dim TodaysDate As Range
    ' Use TargetHeader.Find to ensure we're searching the correct range on the correct sheet
    Set TodaysDate = TargetHeader.Find( _
        What:=CDbl(Today), ' Match Excel's internal date serial number for reliability
        LookIn:=xlValues, ' Search cell values instead of formulas (avoids formula-based date issues)
        LookAt:=xlWhole,
        SearchOrder:=xlByColumns,
        SearchDirection:=xlNext)
    
    ' First check if we found the date before proceeding
    If Not TodaysDate Is Nothing Then
        Dim Target As Range
        Set Target = TodaysDate.Offset(1, 0)
        
        ' Copy values directly (faster than PasteSpecial)
        Target.Resize(Source.Rows.Count).Value = Source.Value
    Else
        ' Optional: Add a message if the date isn't found (helps with debugging)
        MsgBox "Today's date (" & Format(Today, "mm/dd/yyyy") & ") wasn't found in the header range.", vbExclamation
    End If
End Sub

Key Changes Made: #

• Explicit Worksheet Reference: We store "Data Sheet 2" in TargetSheet and use TargetHeader.Find to ensure we're always searching the correct range on the correct sheet, regardless of which sheet is active.
• Check for Nothing: The If Not TodaysDate Is Nothing Then block prevents us from trying to access properties of a non-existent object.
• Date Matching with CDbl(Today): Converting the date to its serial number (what Excel uses internally) avoids format mismatches between displayed dates and stored values.
• Direct Value Assignment: Instead of Copy/PasteSpecial, we assign values directly with Target.Resize(Source.Rows.Count).Value = Source.Value—it's faster and avoids clipboard issues.
• Removed Redundant Check: The If TodaysDate = Today Then check was unnecessary because Find already looked for that value; we just need to confirm the find was successful.

Additional Tips to Prevent Future Issues: #

• Ensure Header Dates are True Dates: Select your header range (D119:HF119), go to Home > Number Format, and confirm it's set to a Date format (not Text). This ensures Excel treats them as date values, not text strings.
• Add Error Handling: Wrap the code in an On Error Resume Next or On Error GoTo block if you want to handle unexpected errors gracefully (though fixing the root causes above should make this unnecessary).

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