Hello,
Can anyone help me please?
How can I add a 3rd criteria to the following code:
I tried adding
Please Login or Register to view this content.
But it never worked.Please Login or Register to view this content.
Please help.
Sorry for being a novice
Regards,
Michael
VBA is constrained by the normal parameters of AutoFilter, and that's two criteria max at one time in one column.
But all is not lost. You can add a new column to your table that does a test on the other column for the 3 values you are testing for and gives a single TRUE response if the value is one of those values. Then you can AutoFilter on that new column to see the rows filtered.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Hello,
thanks for the reply.
The file im working on, is emailed to me daily, and what im trying to do is, create a macro which is activated by an email rule.
this macro will then sort & format the data as requested above, save the file, and re-email it to specified contacts.
this is to enable that the work is done regardless, even if I'm off on holiday or sick.
So id have to write code to insert the new column to filter out TRUE etc etc..
i think it will be easier coding to filter the first 2 criteria, copy and paste to sheet 2, then go back to sheet 1 and do an autofilter for my 3rd criteria.
do you agree?
Regards,
Michael
I think what you've described is how I would do it. As long as you don't need to see all three filtered criteria at the same time and are willing to filter 3 times and copy 3 times, that's what I would do.
Make sure your code takes into account that sometimes there may be NO rows that match one of your criteria. In that case you need to make sure nothing is getting copied. Test for that.
Last edited by JBeaucaire; 07-24-2010 at 10:53 AM.
hello,
Novice here again...
Im struggling now.
Please Login or Register to view this content.
How can i amend this code, so the 2nd lot of filtered data, is copied & pasted below the first lot of filtered data on sheet 2?
Im guessing im going along the right lines, but am missing something really simple.
If BR > 1 Then
.Copy Sheets("ESP").Range(A1").Selection.End(xlDown).Offset(1)
HELP!
much appreciated.
Regards,
Michael
BTW:Please Login or Register to view this content.
That way, on the off chance that there is already a Sheet2 (like if you're sick and someone else is doing this), your code won't overwrite it.Please Login or Register to view this content.
I would vote for foxguy's 2nd option:
Please Login or Register to view this content.
As for adding a sheet with error-checking, this is the technique I use...it tests if the sheet exists already or not...if not it creates it in a single command...if so it clears the sheet to receive your new data.
Please Login or Register to view this content.
I'd prefer advancedfilter: more criteria, builtin copyfacility.
Last edited by snb; 07-24-2010 at 12:04 PM.
I'd prefer advancedfilter: more criteria, builtin copyfacility.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks