mattadams84
11-03-2016, 03:08 PM
I have set up a macro to filter through a sheet 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.
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.