VBA实现两单元格日期差计算：仅当X列有日期时返回结果

No worries at all—this is a super common issue when working with dates in VBA, and it’s easy to fix with a simple check! The problem is that when column X doesn’t have a valid date, trying to calculate a date difference throws an error because VBA can’t perform math on non-date values.

Here’s your modified code that only runs the calculation when column X contains a valid date, plus some extra fixes to make it more reliable for new users:

Sub CalcDays()
    Dim LastRow As Long
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet2")
    Dim i As Long ' Added loop counter variable
    
    ' Fix: Qualify Find with the worksheet to avoid using the active sheet
    LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    ' Loop through each row starting from row 2 (assuming row 1 is headers)
    For i = 2 To LastRow
        ' Check if column X has a valid date
        If IsDate(ws.Cells(i, "X").Value) Then
            ' Replace "Y" with the column containing your second date
            ' This calculates the difference between X and Y (adjust as needed)
            ws.Cells(i, "Z").Value = DateDiff("d", ws.Cells(i, "X").Value, ws.Cells(i, "Y").Value)
        Else
            ' Optional: Clear the result cell if X has no valid date
            ws.Cells(i, "Z").Value = ""
            ' Or you could set a message like: ws.Cells(i, "Z").Value = "No date in X"
        End If
    Next i
End Sub

Key Changes Explained: #

• IsDate Check: The If IsDate(...) line verifies that the cell in column X actually holds a valid date before trying to calculate the difference. If it’s not a date, we skip the calculation entirely.
• Qualified References: All Cells calls are prefixed with ws. to ensure we’re always working on Sheet2, even if another sheet is active. This avoids weird, hard-to-debug bugs that new VBA users often run into.
• Loop Counter: Added i as a loop variable to iterate through each row (I assumed you were looping since your original code referenced LastRow).
• Clean Error Handling: The Else clause clears the result cell (or you can add a custom message) so you don’t get ugly error values cluttering your sheet.

Just adjust the columns in the code to match your actual setup—replace "Y" with the column that has your second date, and "Z" with where you want the result to go.

内容的提问来源于stack exchange，提问作者C. Rad