What’s wrong with this countif procedure ?

[hermac]

Hello,
As part of a larger procedure I want to check if all dates of some
range fall between a lower limit (From) and an upper limit (Till). I
thought this would do it . But no!

Sub test()
Dim DatOr As Range, From As Date, Till As Date
Set DatOr = [E2:E6618]
From = Application.InputBox("Date Begin Year ?", Type:=1)
Till = Application.InputBox("Date End Year ?", Type:=1)
MsgBox Application.CountIf(DatOr, "<" & From) & " " &
Application.CountIf(DatOr, ">" & Till)
DatOr.Select

End Sub

I swear that 31/12/2006 and 1/1/2008 both appear in range DatOr ; all
the other dates vary between 1/1/2007 and 31/12/2007

Funny thing : If I feed 1/1/2007 and 31/12/2007 respectively in the
inputboxes I get 1 and 0 in the MsgBox. Can you figure the 0 ?

Thank you very much for your help!

 

[Dave Peterson]

This worked ok for me when I entered dates in the inputboxes.

Option Explicit
Sub test()
Dim DatOr As Range
Dim From As Date
Dim Till As Date

Set DatOr = ActiveSheet.Range("E2:E6618")

From = Application.InputBox("Date Begin Year ?", Type:=1)

If Year(From) < 2000 _
Or Year(From) > 2020 Then
MsgBox "not a valid From date"
Exit Sub
End If

Till = Application.InputBox("Date End Year ?", Type:=1)

If Year(Till) < 2000 _
Or Year(Till) > 2020 Then
MsgBox "not a valid Till date"
Exit Sub
End If

MsgBox Application.CountIf(DatOr, "<" & CDate(From)) _
& " " & Application.CountIf(DatOr, ">" & CDate(Till))

End Sub

Hello,
As part of a larger procedure I want to check if all dates of some
range fall between a lower limit (From) and an upper limit (Till). I
thought this would do it . But no!

Sub test()
Dim DatOr As Range, From As Date, Till As Date
Set DatOr = [E2:E6618]
From = Application.InputBox("Date Begin Year ?", Type:=1)
Till = Application.InputBox("Date End Year ?", Type:=1)
MsgBox Application.CountIf(DatOr, "<" & From) & " " &
Application.CountIf(DatOr, ">" & Till)
DatOr.Select

End Sub

I swear that 31/12/2006 and 1/1/2008 both appear in range DatOr ; all
the other dates vary between 1/1/2007 and 31/12/2007

Funny thing : If I feed 1/1/2007 and 31/12/2007 respectively in the
inputboxes I get 1 and 0 in the MsgBox. Can you figure the 0 ?

Thank you very much for your help!

 

[Bernard Liengme]

Let's begin with the logic of the problem
If I want to count how may values in E2:E6618 are between 5 and 15
(inclusive)using the COUNTIF function I would use
=COUNTIF(E2:E6618,">=5") - COUNTIF(E2:E6618,">15)
Note that we count (how many are 5 or above) less (how many are above 15)
Or I could use the SUMPRODUCT function
=SUMPRODUCT(--(E2:E6618>=5),--(E2:E6618<=15))

But why use Excel functions when VBA can do all?

Sub test()
Dim DatOr As Range, From As Date, Till As Date
Dim mycount
Set DatOr = [E2:E6618]
From = Application.InputBox("Date Begin Year ?", Type:=1)
Till = Application.InputBox("Date End Year ?", Type:=1)
For Each cell In DatOr
If cell.Value >= From And cell.Value <= Till Then mycount = mycount + 1
Next
MsgBox mycount
End Sub