Type mismatch error when I try to calculate time between two dates-CodePudding

I keep getting the error Run-time error '13': Type Mismatch. I've checked that there are not non-date values in the fields I'm pulling from; they either have a date or show up as (Blanks). I've checked that the date fields are formatted as date fields and that the calc field is numeric.

This calculation works when I try it in the sheet itself, just not in VBA. I can't figure out what it doesn't like.

Sub Date_Calc()

Dim Result, RowNo As Long
Dim FirstDate, SecondDate As Date
Dim Ws As Worksheet: Set Ws = Sheets("Sheet1")
    
RowNo = 2
Column1 = 1
Column2 = 2
Column3 = 3
    
    Do Until RowNo = 10000
    FirstDate = Ws.Cells(RowNo, Column1)
    SecondDate = Ws.Cells(RowNo, Column2)

    If FirstDate <> "" And SecondDate <> "" Then
        Ws.Cells(RowNo, Column3) = Day(SecondDate) - Day(FirstDate)
    RowNo = RowNo   1
    End If
    Loop

End Sub

CodePudding user response：

Try using IsDate to validate your input to Day.

Dim Ws As Worksheet
Set Ws = Sheets("Sheet1")

Dim Column1 As Long, Column2 As Long, Column3 As Long, RowNo As Long
Column1 = 1
Column2 = 2
Column3 = 3
 
For RowNo = 2 to 10000
    Dim val1 As Variant, val2 As Variant
    val1 = Ws.Cells(RowNo, Column1).Value
    val2 = Ws.Cells(RowNo, Column2).Value

    If IsDate(val1) And IsDate(val2) Then
        Ws.Cells(RowNo, Column3).Value = Day(val1) - Day(val2)
    End If
Next