Advanced filter vba with dates

Hello,

I am struggling with an advanced filter problem. I have a workbook containing data on a sheet; a userform allows users to choose criteria which is input into another sheet. The criteria is loaded into the top section of the sheet while the data is copied to the rows below and an advanced filter then filters by the chosen criteria.

My problem is this--the date will not work for me. I have the user choose a start and end date on the form, which is dragged into the criteria formatted with a ">=" and a "<=" respectively for the start and end dates. ex. ">=01-01-16" The problem is that the advanced filter filters out all of the data when the dates are included.

If however, I manually type in ">=01-01-16" the filter works fine. Does anyone have any idea why it does not work when the form populates the criteria for me rather than typing it in manually? I've checked the format of the cells and tested this several times, but nothing seems to help???

Thank you.

Hi Marvin,

Thanks so much but sadly this does not work for me, when I import the date alone (ex. 2016-01-01) into cell Z1 and then use formulas to drag this into the criteria cell like =">="&Z1, it filters out all the results. It also immediately changes the date dragged into the formula into a serial date. I have tried changing the format on the cells to general and various date formats, but nothing seems to work.

When I go into the same criteria cell and manually type in >=2016-01-01, the criteria works perfectly and filters my results as expected. I just cannot figure out any means to drag results in from the form. I have tried dragging into another cell, changing formats, using formulas etc...just have no clue why this won't work for me.

I'd love the see the info in your link, but my security detects a Trojan and blocks it.

The date filters work with the formulas, but they mess up all the other criteria filters. I forgot that the fields must be completely blank for the filter to work properly. My form has a macro in it to clear the fields everytime it is opened, so it was clearing out the formulas for the dates as well. Once the formulas are gone, the dates don't work, and if I leave them in, the remaining filters don't work...grrrrr!

So I am now attempting to write VBA which will look to the date columns I created out of the filter fields, test if they are empty or not (if user input dates on the form), then grab data from the columns with the ">=" with the dates and drop it back into the criteria fields. I am a self-taught and inexperienced VBA user so I cannot seem to get this off the ground either, although I have looked at numerous examples and am unsure where this is going wrong. The start date from the form is in AB2, the end date from the form is in AC2, the start and end dates with the ">=" and "<=" criteria and in AD2 and AE2 respectively and the criteria columns are G and H:

Please Login or Register  to view this content.