Excel批量日期更新求助：如何按指定次数为多行更新日期？

Hey there! Let's tackle this Excel VBA problem you're facing with those frustrating error codes (91, 1004, 5) while handling your 16,000-row dataset. First, let's break down why those errors pop up, then fix the code step by step to get your date-filling task working smoothly.

Common Causes for Your Errors #

Before diving into the code, let's clarify what those error codes usually mean in your context:

• Error 91: Typically happens when you're trying to use an object variable that hasn't been properly set (e.g., not explicitly referencing your worksheet, or a Range object that doesn't exist).
• Error 1004: Almost always tied to invalid Excel operations—like trying to write to a protected cell, referencing a non-existent range, or relying on ActiveSheet/Select which can break if the wrong sheet is active.
• Error 5: Occurs when you make an invalid procedure call, like passing the wrong data type to a function, or having logic that tries to do something impossible (e.g., filling negative rows of dates).

Fixed VBA Code with Explanations #

Here's a revised version of your code that addresses these issues, adds validation, and handles large datasets efficiently:

Sub FillDateRange()
    Dim targetSheet As Worksheet
    Dim lastDataRow As Long
    Dim currentFillRow As Long
    Dim i As Long, dayOffset As Long
    Dim startDt As Date, endDt As Date
    Dim requiredFillCount As Long
    
    ' --------------------------
    ' Configure your settings here
    ' --------------------------
    Set targetSheet = ThisWorkbook.Worksheets("YourSheetName") ' Replace with your actual sheet name
    currentFillRow = 2 ' Start filling dates in B2 (adjust if your header is on a different row)
    
    ' Turn off screen updates to speed up processing and avoid glitches
    Application.ScreenUpdating = False
    ' Disable events to prevent unexpected triggers (optional but helpful for large datasets)
    Application.EnableEvents = False
    
    ' Get the last row with data in column H (no more hardcoding 16000!)
    lastDataRow = targetSheet.Cells(targetSheet.Rows.Count, "H").End(xlUp).Row
    
    ' Error handling to catch issues and clean up settings
    On Error GoTo CleanupAndError
    
    For i = 2 To lastDataRow
        ' Pull values from current row
        startDt = targetSheet.Cells(i, "H").Value
        endDt = targetSheet.Cells(i, "I").Value
        requiredFillCount = targetSheet.Cells(i, "K").Value
        
        ' Validate data before proceeding
        If IsDate(startDt) And IsDate(endDt) And requiredFillCount > 0 Then
            If startDt <= endDt Then
                ' Calculate actual number of days between start and end (inclusive)
                Dim totalDaysInRange As Long
                totalDaysInRange = DateDiff("d", startDt, endDt) + 1
                
                ' Handle mismatch between K column count and actual days
                If requiredFillCount <> totalDaysInRange Then
                    MsgBox "Row " & i & ": Fill count doesn't match date range days. Using actual days instead.", vbExclamation
                    requiredFillCount = totalDaysInRange
                End If
                
                ' Fill dates into column B
                For dayOffset = 0 To requiredFillCount - 1
                    targetSheet.Cells(currentFillRow, "B").Value = DateAdd("d", dayOffset, startDt)
                    currentFillRow = currentFillRow + 1
                Next dayOffset
            Else
                MsgBox "Row " & i & ": Start date is later than end date. Skipping this row.", vbWarning
            End If
        Else
            MsgBox "Row " & i & ": Invalid date or fill count. Skipping this row.", vbWarning
        End If
    Next i
    
    ' Success message
    MsgBox "Date filling completed! Total rows filled: " & currentFillRow - 2, vbInformation
    
CleanupAndError:
    ' Restore Excel settings
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    ' Show error details if something went wrong
    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
    End If
End Sub

Key Improvements in This Code #

• Explicit worksheet reference: No more relying on ActiveSheet—this eliminates Error 91 and 1004 caused by accidental sheet switches.
• Data validation: Checks if dates are valid and fill counts are positive, preventing Error 5 from bad input.
• Efficient processing: Disables screen updates and events to handle 16k rows without lag or crashes.
• Dynamic row detection: Automatically finds the last row of data, so you don't have to hardcode 16000.
• Error handling: Catches issues and restores Excel settings properly, so you don't get stuck with disabled features.

Quick Usage Tips #

1. Replace YourSheetName with the actual name of your worksheet (e.g., "DataSheet").
2. Make sure columns H, I are formatted as dates, and column K is formatted as numbers.
3. Backup your file first before running the code—better safe than sorry with large datasets!

内容的提问来源于stack exchange，提问作者Clément Hurel