Pull data based on user input

Hi Friends,

By using VBA, I am supposed to pull data based on user input, Column is fixed but rows are not fixed.

Based on cell value C5 to C8 (Values will change), data main table should get filtered out.

I have attached example file with more details
Pull data based on cell value.xlsm


Thanks & Regards

Hi Mani

I'm intrigued why Group 2 doesn't get included?

The value is between 80,000 and 160,000 (110,665) and at least one of the entities A thru G has a % share between 10% and 30%.

Also, if your second requirement is true (the % share) then why are entities A and C included in Group 5? (% share < 10% in each case).

Could you perhaps elucidate and refine your requirements? They don't make clear sense at present!

Thanks,

Tony

Hi tfurnivall,

Group 2 is not included because cell P2 value is 40% which is greater than the input value of 40% in cell C8, so that full gr is excluded from the result

And sorry for Group 5, that is a mistake, it should not be included.

Sorry for the confusion.

So, to restate the requirements for your filter, we have the following:

Please Login or Register  to view this content.

Note, of course, that there is no such construct as For every, so we'lll have to code around it!

Does this accurately reflect your constraint? And if we take that second loop (For every) and recode it like this:

Please Login or Register  to view this content.

, does that give you enough help to finish it off on your own?

If so, great!

If not, let's continue to talk!

Tony

Tony, I am new to VBA coding. Your explanation is good, but can you please post the full code.

As on my own i am not able to complete it.

Sorry for the extra work.

I'm always tempted to say, at times like this, "If I write your code, will you send me your paycheck?"

So, I'll get you started, but you need to do most of the work yourself, otherwise you'll never learn anymore, will you!

So here's a spreadsheet with some skeleton code:

Please Login or Register  to view this content.

You'll notice that the first thing I've done is to put all of the very specific cell addresses into constants. THis avoids what are called, in the trade, 'magic numbers'. If you have a magic number and you need to change it, you need to be able to distingish between one meaning of the magic number (say 11) and another. At one time it might mean the number of players on the field for a soccer team, and at another, it might mean the number of months left in the year on February 1st. So rule #1:
ALWAYS GIVE CONSTANT VALUES A NAME - that way when they change (which is why they're called constants, right ) you only have to change them in one place. In your case, there are a lot of constants involved, but if your boss decides to add three more columns of data, you'll only need to change a few constants (depending on where those columns get added, and what they are!)

Second - you have indicated that both constraints need to be true (Value AND Share). So I've coded it this way. If either one needs to be true (or if ANY share needs to match the Share constraints) then the logic changes - quite dramatically. But again, there's only a limited number of places that need to change.

Third, some parts of the code need considerably more detailed calculation than others. Using a technique called Stepwise refinement, we simply shunt the active development of those parts off until later. You'll see this happen as I need to define a Group, a Share and an Extract. Simply declare the function or procedure, and then worry about coding it later. (You'll note that I've only developed one of the three functions - the others are for you to do!)

Lastly, always try and run your code as early and as often as possible. It's much easier to find problems from code that is running. Exceedingly true in COBOL programs (which are sometimes many thousands of lines long), but equally true in VBA. Sprinkle your code with debug statements, so you can see how things are progressing.

So, take a look at this code (it's in the attached spreadsheet), and see how you go on filling out some of the gaps,

HTH

Tony

PS I really don't want to deprive you of your paycheck, but you might want to give me some reputation...