I have the same dates and times in two columns. Now I want to loop through the dates and times of the second column and count how many items in the first column ("A1:A10") are greater than the respective date and time in the loop. But the count is always zero ("C1:C10").

CountIf with dates and times

Sub countif_datetime()

Dim sheet1 As Worksheet
Dim my_range As Range
Dim i As Integer

Set sheet1 = Worksheets("Sheet1")
Set my_range = sheet1.Range("A1:A10")

For i = 1 To 10
    sheet1.Cells(i, 3).Value = WorksheetFunction.CountIf( _
        my_range, ">" & sheet1.Cells(i, 2).Value _
    )
    Next i
    
End Sub

When I use the same function (countif) in the worksheet ("D1:D10") the count is there.

I also tried to convert the dates to double and it did not work either.

CodePudding user response：

I can't reproduce your error at my end, i.e. your code works for me

but since the formula works at your end, you could consider a formula approach

Sub countif_datetime()

    With Worksheets("Sheet1")
        With .Range("A1:A10").Offset(, 2)
            .FormulaR1C1 = "=COUNTIF(R1C1:R10C1,"">"" & RC[-1])"
            .Value = .Value ' turn formulas into values
        End With
    End With
End sub

or, with a slightly more general approach:

With Worksheets("Sheet1")
    With .Range("A1:A10").Offset(, 2)
    
        Dim firstRow As Long, _
            lastRow As Long
            firstRow = .Rows(1).Row
            lastRow = .Rows(.Rows.Count).Row
        .FormulaR1C1 = "=COUNTIF(R" & firstRow & "C1:R" & lastRow & "C1,"">"" & RC[-1])"
        .Value = .Value

    End With
End With