VBA CountIf Function

[mikeymay]

I have a worksheet that contains some product prices with a Date From value.

Currently all the dates in the table are set to 1 Jan 25 except the final row of the table (row 3478) set as 2 Jan 25.
The column containing the dates is formatted as a date format

Using the TODAY() funtion that returns todays date of 7 Jan 25, when I do an Excel function to count the number of instances of a date value > todays date (7 Jan 25) - 6 days to establish how many instances of dates >1 Jan 25, I get a return of 1 as based on the above image

And when i want to see how many instances there are of date values > TODAY()-7 (31 Dec 24) I get a return of 3,472 which is the exact count or rows in the table

When I want to do the same in VBA
The current date - 6 days (i.e. 1 Jan 25) returns 1 as expected

But doing the same for the current date - 7 (i.e. 31 Dec 24) I get 0 when it should return 3,472

This makes no sense at all and I have no idea what Excel/VBA is doing.....

Can anyone shed some light on this?


TIA

 

[Joe4]

Which VBA module are you running that from?
A sheet module? A general module?
Are you sure that the sheet with your data is the active (selected) sheet when you run the code?

If I create a General module, drop this code in it, and make sure I am on the sheet with my data and run the code, it returns the expected results for me:

Sub MyTest()
    MsgBox WorksheetFunction.CountIf(Range("E7:E3478"), ">" & Date - 7)
    MsgBox WorksheetFunction.CountIf(Range("E7:E3478"), ">" & Date - 6)
End Sub

This returns two messages boxes, with the counts of each scenario, and it works as expected for me.

 

[mikeymay]

I am using the immediate windows to test but the the code that I am running where I experienced the issue is in a user form.

The dtDateFrom variable is declared publicly in a General module.

 

[Alex Blakenburg]

If @Joe4's code does not give you the correct result try adding CLng. eg

Sub MyTest()
    MsgBox WorksheetFunction.CountIf(Range("E7:E3478"), ">" & CLng(Date - 7))
    MsgBox WorksheetFunction.CountIf(Range("E7:E3478"), ">" & CLng(Date - 6))
End Sub

 

[mikeymay]

Rather than trying to establish why this isn't working as it should, I'm going to drop the variables used in the COUNITFS into cells and watch the cell containing the formula.

This works as expected so I'll use this is the solution.

I am curious as to why the VBA version doesn't work though...

 

[Alex Blakenburg]

Did you try my version ?
You are in the UK which means that your date format is most likely dd/mm/yyyy, since VBA works in US Date format it is likely that the issue is related to the date format being supplied to the CountIf formula using VBA doesn't match.
Using CLng should fix that.