Stop advanced filter when there is no criteria match

[J4Y]

Hi can someone kindly assist.

My Objectives are:

a) Advance filter on worksheet called B.Derivative Exposure using a criteria I have on another worksheet
b) If the criteria is met then filter all the data under headings which are in row B1:E1 on the worksheet called "Bloomberg - Derivative"
c) And then for every filtered row in "Bloomberg - Derivative", starting from cell F2, populate formulas from F2 to K2 down to the last filtered row, using column A to infer the last row
d) And if the criteria is not met, then only expect the formula code to populate the formula columns, in row 2 under the headings, from cells F2 to K2 ONLY.


Problem:

When I run the macro, if the criteria is not met I get no data - as you would expect - under the headings in B1 to E1, but the formula code overrides the headings from cell F1 to K1 with the formulas and does so from F2 to K2 as well.

I've been racking my brains all day to see how I can stop excel from doing this to my headings when the criteria is not met.

Solution:

The solution would be to tell excel that if the criteria is not met to go no further and not to populate the formulas in cell f2 to k2 until the last row. The problem is I am not that sophisticated to write that code. Would someone be kind enough to assist?

Here's the code that I currently have:

Please Login or Register  to view this content.

Many thanks,

Jay

[Norie]

Why not check if there are any results?

If there are insert the formulas, if there aren't don't.

To check could be as simple as seeing if there's a value in row 2 on the results sheet after the filter.

You might just need to check what X is in your code.

If it's 1 don't put the formulas in.

[Pete_UK]

Can't you use the value of X after applying your advanced filter to determine whether to apply your formulae? Something like this:

Please Login or Register  to view this content.

Hope this helps.

Pete

[J4Y]

Nori/ Pete .......... thanks a mil.

Pete I used your code - replacing the 2 with a 1 - and it works a treat.

Cheers guys.

P.S If any of you guys know how to speed that code up, let me know.... I'm not familiar with variables but I am sure that the formula codes can be read once to memory and the results sent in one go...

[Norie]

As far as I can see you are already sending the formulas in one go.

[J4Y]

Thanks Nori