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:


Sub Test_Code()

    Dim X As Long
   
    Sheets("Bloomberg - Derivative").Select
    Sheets("B.Derivative Exposure").Columns("A:BB").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Filter Information").Range("AQ1:AQ2"), CopyToRange:= _
        Range("B1:E1"), Unique:=False
       
       
    X = Cells(Rows.Count, "A").End(xlUp).Row
   
   
    Range("F3:F" & X).FormulaR1C1 = "=LEN(RC3)"
    Range("G3:G" & X).FormulaR1C1 = "=IF(RC6="""","""",IF(RC6=9,IF(MID(RC3,6,1)=""1"",(LEFT(RC3,5)&"" ""&MID(RC3,6,3)&"".""&RIGHT(RC3,1)&"" comdty""),IF(MID(RC3,6,1)=""9"",(LEFT(RC3,5)&"" ""&MID(RC3,6,2)&"".""&RIGHT(RC3,2)&"" comdty""),""No data"")),LEFT(RC3,5)&"" ""&MID(RC3,6,3)&"" comdty""))"
    Range("H3:H" & X).FormulaR1C1 = "=IF(RC7="""","""",BDP(RC7,R1C))"
    Range("I3:I" & X).FormulaR1C1 = "=IF(RC7="""","""",BDP(RC7,R1C))"
    Range("J3:J" & X).FormulaR1C1 = "=IF(RC7="""","""",IF(LEN(RC9)=3,BDP(LEFT(RC9,1)&"" ""&MID(RC9,2,3)&"" comdty"",R1C),IF(LEN(RC9)=4,BDP(LEFT(RC9,4)&"" comdty"",R1C),"""")))"
    Range("K3:K" & X).FormulaR1C1 = "=IF(RC7="""","""",BDP(RC7,R1C))"
   
    Range("G2").Select


Many thanks,

Jay