VBA报错Error 1004：遍历行获取每行最大日期失败求助

Let's break down what's going wrong with your code and fix it step by step:

Key Issues in Your Current Code #

• Variable Name Conflict: You're using row both to store the total number of rows (row = Source.Rows.Count) and as your loop variable (For Each row In CellRange). This confuses VBA and leads to unexpected behavior that can trigger errors.
• Incorrect Range Targeting: You set CellRange to the entire W:AC columns, then try to pull the max value of that whole massive range for every iteration. What you actually need is the max of each individual row's W:AC cells, not the entire column set.
• Unqualified Range Reference: When you write Range("BU").Value, you don't specify which sheet this range belongs to. If another sheet is active when the code runs, it'll try to write there instead of Sheet1—this is a common cause of Error 1004.
• Misaligned Loop Logic: Your For Each loop iterates over every single cell in W:AC, not every row you care about. That's way more iterations than needed and doesn't target rows correctly.

Corrected Code #

Here's a revised version that fixes all these problems:

Sub Max_Date()
    Dim MaxDate As Variant ' Use Variant to handle cases where no valid dates exist
    Dim Source As Worksheet
    Dim lastRow As Long
    Dim currentRow As Long
    
    Set Source = ActiveWorkbook.Sheets("Sheet1")
    ' Get the last row with data in column W (avoids looping through empty rows)
    lastRow = Source.Cells(Source.Rows.Count, "W").End(xlUp).Row
    
    ' Loop through each row with data
    For currentRow = 1 To lastRow
        ' Target only the W:AC cells for the current row
        With Source.Range("W" & currentRow & ":AC" & currentRow)
            ' Use Application.Max instead of WorksheetFunction.Max to avoid hard errors
            MaxDate = Application.Max(.Cells)
        End With
        
        ' Write result to the corresponding BU cell, handle non-date cases
        Source.Range("BU" & currentRow).Value = IIf(IsDate(MaxDate), MaxDate, "")
    Next currentRow
End Sub

What Changed? #

1. Clean Variable Naming: Renamed conflicting row variables to lastRow (for storing the last used row) and currentRow (for looping) to eliminate confusion.
2. Row-Specific Ranges: For each iteration, we only target the W:AC cells in the current row, not the entire columns.
3. Sheet-Qualified References: Every range is prefixed with Source to ensure we always work on Sheet1, no matter which sheet is active.
4. Error Resilience: Switched to Application.Max (instead of WorksheetFunction.Max) because it returns an error value (instead of crashing) if no valid dates are found. We then use IIf to write an empty string if the result isn't a valid date.
5. Efficient Row Detection: Used End(xlUp) to find the last row with data in column W, so we don't waste time looping through thousands of empty rows.

This should resolve the Error 1004 and correctly write the max date for each row's W:AC range into the corresponding BU cell.

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