LinkBack URL
About LinkBacks
Hi all,
Referring to this thread which started with formula to powerquery to VBA.
https://www.excelforum.com/excel-for...ml#post4780688
Can anyone get the filter/criteria to work using the attached file?
Hi all,
Referring to this thread which started with formula to powerquery to VBA.
https://www.excelforum.com/excel-for...ml#post4780688
Can anyone get the filter/criteria to work using the attached file?
Last edited by joelimzh; 11-23-2017 at 05:09 AM.
how much data are you talking about here?
the main issue i can see is you have year set by the sheet name rather than a column in the table
can you not create a master sheet - add year as a column field and then filter that?
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Hi,
Do you want automatic wildcards on all filter fields, or should the user supply them if needed? With a small rearrangement of the filter field area, using advanced filters should greatly simplify the code.
Don
Please remember to mark your thread 'Solved' when appropriate.
Hi,Originally Posted by humdingaling
On my real data, I have about "600 rows of data on each sheet" which is created on a yearly basis and I need to have at least 7 years of records which is why its being sorted out in sheet name "by year".
youre awake early (or late to sleep) for uk time
if i expand to even 10 years
10*600=6000 rows x 5 columns (4 of your own + 1 for year to be inserted) =~30,000 cells of data
that's not really that big
i think the consolidated model can work
what do you think about xlnitwit's quote
do you mind if the format is re-arranged slightly?With a small rearrangement of the filter field area, using advanced filters should greatly simplify the code
not quite sure what you wanted with regards to wildcards
anyways the consolidation and advanced filter basics below
Please Login or Register to view this content.
Hi,
What you did was great.
However the search portion isn't suitable as some course title could be quite long and that could be a bit of a mess, which is why I arranged it in that way in my original sample.
Another thing I notice is that, the search seems to be "exact" rather than "contain".
I.e. if i search for "pple" under course title - Apple, Bapple, Capple anything with "pple" should return.
if that search thing is not compatible we can certainly just add in code to copy/transpose to some area in the workbook which will not be used (or just out right delete it)
this is no big issues there
this refers to the question raised by xlnitwit of which you did not answerI.e. if i search for "pple" under course title - Apple, Bapple, Capple anything with "pple" should return.
this should not be too complex either but before coding thisDo you want automatic wildcards on all filter fields, or should the user supply them if needed?
have a bit of a read/think of advanced filters
http://www.contextures.com/xladvfilter01.html
and then come back with exactly what wildcards you want where
it sounds like you just want the vanilla *pple* search
Last edited by humdingaling; 11-22-2017 at 07:03 PM.
some quick adjustments to the code
added
Please Login or Register to view this content.
use the original column B to fill in search terms
you can hide columns G to K if you wish
This is great. thank you so much!
and yes, i have read about the advanced filter though I'm still confused about the portion about wildcard.
please play around with it and test out the scenarios you will have in real data/searches
slight change in the code to account for year
*2017* in advanced filter does not work so i changed it back to just search "2017"
as it is a number and not text you cannot search "201*" for instance
^this should fix that bit upPlease Login or Register to view this content.
Hi, yes. I am playing around with the codes and real data I have now. seems to work great.
Hi, I done with sorting out my real data and it works great!
Is there anyway or code I can exclude certain sheets?
e.g. I have sheets 2017,2016,2015,2014,2013..... 2005.
I want to exclude sheets from 2010 to 2005.
did you want to control what you want to exclude based on cells?
ie have a from-To range? or you want it hard coded into VBA?
this part of the code already excludes certain sheetsPlease Login or Register to view this content.
if you have more sheets to exclude then i would suggest a select-Case statement
so it knows what to do with any sheets
Hi,did you want to control what you want to exclude based on cells?
ie have a from-To range? or you want it hard coded into VBA?
this part of the code already excludes certain sheetsPlease Login or Register to view this content.
if you have more sheets to exclude then i would suggest a select-Case statement
so it knows what to do with any sheets
I have a history sheets from 2005 to 2010 which i need to be in the same workbook, but i don't need them to be in the search/consol function.
and if next year comes, i need to move 2011 to the history sheets and add a new sheet call 2018.
Is there anyway i can have a box/cell where I can let the coding knows the range of sheets i want them to look at?
i.e. 2010 to 2017
in concept it can workPlease Login or Register to view this content.
i made some changes to the code on the sample file
things to note...you need the inputs for FROM and TO whether that is in code or thru data validation or something
otherwise you will have all sorts of things unintended things happen
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks