Date AutoFilter Not Working

[nivota]

Purpose of my script:

To filter Column C3 of my spreadsheet to display only the future dates of meetings

Due to my region is in UK, I understands that the Excel Macro needs special coding to change the date format to DDMMYYYY

My code:

Please Login or Register  to view this content.

Issue: When I run the script, the spreadsheet automatically jumps to the next empty field and does not perform what I intend to do.

I am a very newbie in Excel VBA appreciate for your kind assist to point out and correct whatever mistakes in my code? Many thanks.

[AB33]

Please Login or Register  to view this content.

It might not work as dates are nightmare to deal with. The criteria also works well with texts, so perhaps changing it in to text format will work.

[Stormin']

Some problems:

1. You are building the date from dDate, which is an unset variable instead of Date, which is a function that returns the system date.
2. You should add CLng() around dDate to ensure it is coerced to long integer format (and it shows more clearly what you are aiming to do)
3. The function DateSerial is being used the wrong way round. It should be DateSerial(Year,Month,Day)
4. (optional) You can just go straight to lDate = CLng(Date) since VBA will convert the system date format to long integer using the system date format.
5. You are passing the criteria as a string, and using dDate instead of lDate


Implementing the changes should fix the code:

Please Login or Register  to view this content.

[xlnitwit]

Hi,

If you are using a Long variable, you can skip a conversion by using CLng instead of CDbl.

[Stormin']

Oops, nice catch nitwit. My mistake. I have edited my code above.