• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code for Advance Filter is not giving proper result

PRABHAKAR9020

New Member
I am enclosing here a excel file in which Sheet name "MDB" is for data base and sheet name "filter_data" is for getting the result after applying advance filter.
but i am not getting the accurate result.

may kindly help for the same.

 
You need to be careful when working with dates in programming.
Take a look at your date criteria range. There is no date, which will satisfy below criteria.

73020

To ensure that date criteria works as intended. Following change should be made in your get_nit_date()
yyyy-mm-dd is universally accepted as date string, regardless of system region setting.

Code:
Sub get_nit_date()
If Range("B2").Value <> "" Then
Range("B5").Value = ">=" & Format(Range("B2").Value, "yyyy-mm-dd")
Else
Range("B5").Value = ""
End If

If Range("C2").Value <> "" Then
Range("C5").Value = "<=" & Format(Range("C2").Value, "yyyy-mm-dd")
Else
Range("C5").Value = ""
End If

End Sub

This will change your date criteria to...
73021
 
Dear Sir,
Thanks a lot for your valuable reply.
but i am still facing problem.
- 1st of all i want to store the date in DD-MM-YYYY format in may data base. kindly refer the sheet name "MDB".
- Then i want to use the advance filter method to short out the data based on the multiple criteria range as mentioned on sheet name as "Filter_Data"
- I made the change in micro as per your valuable guidance but code is not giving error.
- i want the data criteria to be entered in DD-MM-YYYY format.
- As per Advance filter micro, i have to mentioned the data criteria, i.e start date as "greater or equal to date value" and end data as "less than or equal to date value".
- get_nit_date() micro was writeen to just capture the greater than or equal to sign or less than or equal to signed. in fact that code is not required. i had to simply write the date value with >= or <= in cell no. B5 and C5 of sheet name as "filter_Data". but when i put the date manually then also advance filter result not work so i though that i should write a code for capturing these >= or <= operator along with date.

It would be great help, if you please spare your valuable time and have a look in my data base sheet, whether i am doing mistake in date format there or or i am doing mistake in criteria sheet.
I shall be highly obliged if you please short out what i am doing wrong. i am very beginner in VBA coding.

updated excel file is enclosed for reference.

With Regards
Prabhakar
 
Last edited:
Dear Sir,
Thanks a lot, i was wrongly writing your suggest code in my file. i will update it as per your valuable guidance and i hope i will work as i have test the same in another file.
once again a lot of thanks.

i will further update if i will find the problem.
can you please help me code that when anyone enter the date in my data base sheet names as "MDB" then only they will be allowed to enter the date in DD-MM-YYYY format and no other format will be accepted.

With Regards
Prabhakar
 
In Excel or most programming paradigm, dates are stored in date value (ex: In Excel it's integer value, 1 day = 1, starting from 1/1/1900 = 1). And only applied specific format for interpretation (string representation of date) or for display.

If you want to validate date entry up front. Best approach is to use some custom date picker to ensure date value is enforced.
As it's difficult to validate if 12/01/20 is Dec 1, 2020 or Jan 1, 2020 etc.

You can find one such example in The Valult.
Fully Customizable VBA Date Picker | Chandoo.org Excel Forums - Become Awesome in Excel
 
Dear Sir,
Thanks a lot.
As you have suggest that i have to be cautious for DATE value in criteria and i would have to use Format command as

Range("........").Value = "<=" & format(Range("......").Value, DD-MM-YYYY)

Similar, i also want to apply the advance filter with criteria the estimate value >=xx and <=yy and want to filter the data.
73052

At present i have not written any code, i have decided to put the value in green cell with >= and <= sign. Whether it will work or i have to write the VBA code similar to get_nit_date() micro with changes and i could to use some other value in place of DD-MM-YYYY. if so what would be format for taking value for this purpose.

You valuable reply shall be highly solicited.

Thanking You
Prabhakar
 
Last edited:
Back
Top