Excel VBA日期对比代码问题：Error工作表输出异常求助

Hey Nick, let's work through your VBA problems step by step. It sounds like you've got the core logic working for the PPDCI sheet, but the Error sheet has messy output, and adding the empty-date check is throwing runtime errors. Here's what's likely going wrong and how to fix it:

1. Why the Error Sheet Has Bad Output #

The anomalies (blank rows, missing ID/name data, partial columns) almost always come down to two key issues:

• Incorrect row counter increment timing: If you're incrementing your Error sheet row counter (let's call it m) before confirming you've written valid data, you'll create blank rows.
• Mismatched copy ranges: When sending data to Error, you're probably not copying all required columns (like ID/name from A-C) before adding the "REVIEW PPD DATA" label.

2. Fixing the Runtime Error on Empty Date Checks #

The error when adding empty-date logic is likely due to:

• Using the wrong method to check for empty cells (e.g., ="" instead of IsEmpty() for truly blank cells)
• A misplaced GoTo label (it needs to exist within the same procedure, and positioned correctly relative to the code block)

3. Corrected Code Example #

Here's a revised version of your code that fixes these issues, with comments explaining key changes:

Sub CompareDates()
    Dim wsData As Worksheet, wsPPDCI As Worksheet, wsError As Worksheet
    Dim lastRow As Long, i As Long, ppdciRow As Long, errorRow As Long
    Dim dateAW As Variant, dateBA As Variant
    
    ' Initialize worksheet references
    Set wsData = ThisWorkbook.Worksheets("Data")
    Set wsPPDCI = ThisWorkbook.Worksheets("PPDCI")
    Set wsError = ThisWorkbook.Worksheets("Error")
    
    ' Get last row in Data (use a reliable column like A if AW has blanks)
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
    
    ' Set starting rows for target sheets (skip headers)
    ppdciRow = wsPPDCI.Cells(wsPPDCI.Rows.Count, "F").End(xlUp).Row + 1
    errorRow = wsError.Cells(wsError.Rows.Count, "A").End(xlUp).Row + 1
    
    ' Loop through Data rows (skip header row 1)
    For i = 2 To lastRow
        ' Skip empty rows in Data (check key column like ID in A)
        If IsEmpty(wsData.Cells(i, "A").Value) Then GoTo NextRow
        
        ' Get date values (use Variant to handle empty cells)
        dateAW = wsData.Cells(i, "AW").Value
        dateBA = wsData.Cells(i, "BA").Value
        
        ' Check for empty dates OR equal dates
        If IsEmpty(dateAW) Or IsEmpty(dateBA) Or dateAW = dateBA Then
            ' Copy ALL required columns (A-C for ID/name, F-H for data) to Error sheet
            wsData.Range("A" & i & ":C" & i & ",F" & i & ":H" & i).Copy _
                Destination:=wsError.Cells(errorRow, "A")
            
            ' Add review label (adjust column to your needs, e.g., column J)
            wsError.Cells(errorRow, "J").Value = "REVIEW PPD DATA"
            
            ' ONLY increment errorRow AFTER writing valid data
            errorRow = errorRow + 1
        Else
            ' Copy F-H to PPDCI if one date is larger
            wsData.Range("F" & i & ":H" & i).Copy _
                Destination:=wsPPDCI.Cells(ppdciRow, "F")
            
            ' Increment PPDCI row counter AFTER writing
            ppdciRow = ppdciRow + 1
        End If
        
NextRow: ' Label to skip empty Data rows
    Next i
    
    ' Cleanup
    Set wsData = Nothing
    Set wsPPDCI = Nothing
    Set wsError = Nothing
End Sub

4. Key Fixes Explained #

• Row counter logic: ppdciRow and errorRow only increment after we've successfully copied data to the target sheet—no more blank rows.
• Copy range clarity: For the Error sheet, we explicitly copy A-C (ID/name) and F-H (data) so all required info is included alongside the review label.
• Empty row skip: We add a check to skip rows in Data where the ID column (A) is empty, preventing garbage entries in Error.
• Safe empty date check: IsEmpty() correctly identifies truly blank cells, avoiding runtime errors from comparing empty values.

5. Additional Tips #

• If your "REVIEW PPD DATA" label is appearing without data, make sure you're copying the data columns before writing the label (not the other way around).
• If you still need a GoTo for empty ranges, ensure the label is inside the same procedure and positioned correctly (like the NextRow label above).

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