0

I have set up a macro to filter through an excel worksheet and to copy across rows that meet certain criteria to another sheet. Here is the marcro for that:

Sub O15Filter75() 
Application.ScreenUpdating = False 
Sheets("O1.5 (Filter 2)").Range("A5:AO" & Sheets("O1.5 (Filter 2)").Range("A" & Rows.Count).End(xlUp).Row).ClearContents 
With Sheets("O1.5").Range("A4:AO" & Sheets("O1.5").Range("A" & Rows.Count).End(xlUp).Row) 
    .AutoFilter 8, ">=" & Sheets("FILTERS").Range("D7") 
    .AutoFilter 9, ">=" & Sheets("FILTERS").Range("D8") 
    .AutoFilter 10, ">=" & Sheets("FILTERS").Range("D8") 
    .AutoFilter 11, ">=" & Sheets("FILTERS").Range("D9") 
    .AutoFilter 12, ">=" & Sheets("FILTERS").Range("D10") 
    .AutoFilter 13, ">=" & Sheets("FILTERS").Range("D11") 
    .AutoFilter 14, ">=" & Sheets("FILTERS").Range("D12") 
    .Offset(1).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(12).Copy Sheets("O1.5 (Filter 2)").Cells(5, "A") 
    .AutoFilter 
End With 
Application.ScreenUpdating = True 
End Sub

I now want to add a different filter in to that code that would need to calculate something. I want to create a filter that works out if the value in a row in column S is in between the values of column T and U. This is a calculation that i am not performing within the excel sheet, it is something i would like to do in VBA, however i cant work out how to do it using the autofilter because there is technically no columun with that data in to filter.

This is the formula that i would use if i had a column in the excel sheet:

=IF(Sxx=MEDIAN(Sxx,Txx,Uxx),TRUE,FALSE)

If anyone can help me integrate that in to my VBA code i ould be grateful.

MA84
  • 97
  • 8
  • 1
    Use Excel flexibility. Insert auxiliary column, fill it with formula calculating if the value in a row in column S is in between the values of column T and U, and returning `False` or `True`. Then add a filter by that column. I guess SQL is more suitable for the task. – omegastripes Nov 03 '16 at 22:36
  • @omegastripes And you [can use SQL against Excel data](http://stackoverflow.com/a/40360055/111794). – Zev Spitz Nov 03 '16 at 23:16
  • But I'm confused. Each successive autofilter is filtering the values in the next **column** (8-14), but against the values in successive **rows** (D7-D12). Is this what you want? – Zev Spitz Nov 03 '16 at 23:21
  • Note: Your formula `=IF(Sxx=MEDIAN(Sxx,Txx,Uxx),TRUE,FALSE)` is equivalent to `=Sxx=MEDIAN(Sxx,Txx,Uxx)`, which is equivalent to `=Sxx=MEDIAN(Sxx:Uxx)`. – YowE3K Nov 04 '16 at 02:22

0 Answers0