Someone tried to record a macro to find the records from a certain date. Since finding dates requires the date format to match exactly, a better way to go would be using Advanced Filter. In Episode 871, I show how to use AdvancedFilter in a macro to extract records from a certain date.
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Finding dates is just a weird thing in Excel.
You have to match the format exactly.
I had someone send in a note, he said that he recorded a Macro that found the thirtieth of a particular date, and when he played that Macro back, it was giving him a debug errors.
That's Macro2 here, Run that.
"Object variable or With block not set".
And when I looked into this, the day that he happened to record the Macro, his dates must have been formatted with two digits for the month; and now the dates are formatted with a single digit for the month, and that causes Excel to not find a match.
Isn't that frustrating?
But that's the way that Edit Find works when we're looking for formatted numbers or formatted date.
I mean, what's frustrating with this is, you know, when you run that Macro, there may be data where that date isn't found.
And so I said, "You know, rather than use the Macro recorder, why don't we go through and just use a completely different approach-- a completely different approach.” And, basically, so, what this Macro does is, it says, "We have a data set and it's going to find the final row in the final column, basically, by going to the last row, hitting the N key, and pressing Up." So, we'll see that in this particular case, final row-- final row-- is 308, and final column is 7.
So what we do then, is set up some criteria.
So, the criteria column is 2, plus my final row, and I build a little criteria section there, basically copying the day from Cell A1 over to I1, and I enter 9/30/2008; click OK, and that gets entered in Excel.
Then I set up an Output Range.
The Output Range is going to be all of the headings from the original data set.
So here's my Input Range, A through G; here's my Criteria Range in column I; and then my Output Range in K through Q.
And now I'm going to use a function that most of us don't use in the Excel interface, but it's a very powerful function called AdvancedFilter.
It's one line of code.
Basically, we're going to do a AdvancedFilter doing an xlFilterCopy, specifying the Criteria Range.
And when we do that, what we're going to see is that we get, from the original data set, just the dates that fall on the data that we input-- so there's 9/32/2008.
Clear out the Criteria Range, Cut the Output Range, and then add a brand new workbook, and paste doing it Auto Fit.
And what we should see is, now we have the records in a brand new workbook for 9/30/2008.
And the way that this works is, it's a bit more generic in that if we ask for a date that's not there, it's still going to keep running-- it's not going to give us any sort of an error, we're just going to get a blank workbook without any records at all.
So, using AdvancedFilter is a bit more friendly than using Fine-- we don't have to worry about how that data is formatted out there in Column A, the filter is actually looking at the underlying value and it's going to work every single time.
There you have it, a better way to find dates within the data set.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Finding dates is just a weird thing in Excel.
You have to match the format exactly.
I had someone send in a note, he said that he recorded a Macro that found the thirtieth of a particular date, and when he played that Macro back, it was giving him a debug errors.
That's Macro2 here, Run that.
"Object variable or With block not set".
And when I looked into this, the day that he happened to record the Macro, his dates must have been formatted with two digits for the month; and now the dates are formatted with a single digit for the month, and that causes Excel to not find a match.
Isn't that frustrating?
But that's the way that Edit Find works when we're looking for formatted numbers or formatted date.
I mean, what's frustrating with this is, you know, when you run that Macro, there may be data where that date isn't found.
And so I said, "You know, rather than use the Macro recorder, why don't we go through and just use a completely different approach-- a completely different approach.” And, basically, so, what this Macro does is, it says, "We have a data set and it's going to find the final row in the final column, basically, by going to the last row, hitting the N key, and pressing Up." So, we'll see that in this particular case, final row-- final row-- is 308, and final column is 7.
So what we do then, is set up some criteria.
So, the criteria column is 2, plus my final row, and I build a little criteria section there, basically copying the day from Cell A1 over to I1, and I enter 9/30/2008; click OK, and that gets entered in Excel.
Then I set up an Output Range.
The Output Range is going to be all of the headings from the original data set.
So here's my Input Range, A through G; here's my Criteria Range in column I; and then my Output Range in K through Q.
And now I'm going to use a function that most of us don't use in the Excel interface, but it's a very powerful function called AdvancedFilter.
It's one line of code.
Basically, we're going to do a AdvancedFilter doing an xlFilterCopy, specifying the Criteria Range.
And when we do that, what we're going to see is that we get, from the original data set, just the dates that fall on the data that we input-- so there's 9/32/2008.
Clear out the Criteria Range, Cut the Output Range, and then add a brand new workbook, and paste doing it Auto Fit.
And what we should see is, now we have the records in a brand new workbook for 9/30/2008.
And the way that this works is, it's a bit more generic in that if we ask for a date that's not there, it's still going to keep running-- it's not going to give us any sort of an error, we're just going to get a blank workbook without any records at all.
So, using AdvancedFilter is a bit more friendly than using Fine-- we don't have to worry about how that data is formatted out there in Column A, the filter is actually looking at the underlying value and it's going to work every single time.
There you have it, a better way to find dates within the data set.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
