CODEWITHSUNDEEP
excelvba
aoswald
aoswald

Reputation: 75

type mismatch error when comparing dates

This code produces a Type mismatch error on the second time through the For Next Loop. I don't know why.

    Dim HD As Integer
    Dim HireDate2 As Date
    Dim HireDate3 As Integer
    Dim today As Date
    today = Now()
    HD = 2
    For HD = 2 To HireDate
        HireDate2 = Range("Z2:Z" & HD)
        MsgBox "Hire Date is " & HireDate2
       If (HireDate2 > today) Then
            HireDate3 = HireDate3 + 1
        End If
        HD = HD + 1
    Next HD

Thanks in advance for your help

Upvotes: 0

Views: 993

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

Try the code below :

Dim HD As Long
Dim HireDate2 As Date
Dim HireDate3 As Long
Dim MyToday As Date
Dim HireDate As Long

MyToday = Date  ' <-- get today's date into a variable

For HD = 2 To HireDate
    HireDate2 = Range("Z" & HD).Value
    MsgBox "Hire Date is " & HireDate2
    If DateDiff("d", MyToday, HireDate2) > 0 Then ' <-- if HireDate 2 is in the future
        HireDate3 = HireDate3 + 1
    End If
Next HD

Upvotes: 1

Related Questions