VBA macro that filters two criteria in one column and grabs top 8 in another column

[bored622]

Hello everyone,

I'm trying to make a macro button that when clicked on it filters two criteria from column M and filters the top eight from column C.

This is what I got so far:

Sub Filterfp()

With Worksheets("WaveFile").Range("A6")
.AutoFilter Field:=13, Criteria1:="FP", Operator:=xlOr, Criteria2:="BK"
.AutoFilter Field:=3, Criteria1:="8", Operator:=xlTop10Items

End With

End Sub

 

[Peter_SSs]

Would there be a way to convert from C7 down from text to numbers in VBA?

Try this

Sub Filterfp_v3()
  Dim dVal As Double

  With Worksheets("WaveFile").Range("A6").CurrentRegion
    .Columns(3).Replace What:="-", Replacement:="", Lookat:=xlPart
    .AutoFilter Field:=13, Criteria1:="FP", Operator:=xlOr, Criteria2:="BK"
    dVal = .Worksheet.Evaluate(Replace("AGGREGATE(14,6," & .Columns(3).Address & "/((#=""FP"")+(#=""BK"")),8)", "#", .Columns(13).Address))
    .AutoFilter Field:=3, Criteria1:=">=" & dVal
  End With
End Sub

This is what I ended up with from that earlier sample data (I've hidden the other columns to keep the mini sheet small)

bored622.xlsm
	C										M
6	DPCI										Pull Mode
7	97110681										FP
8	31110815										FP
9	31110851										FP
10	31111021										FP
14	31110297										BK
15	31110480										BK
16	31110676										BK
17	31110678										BK
26
WaveFile

 

[bored622]

Try this

Sub Filterfp_v3()
  Dim dVal As Double

  With Worksheets("WaveFile").Range("A6").CurrentRegion
    .Columns(3).Replace What:="-", Replacement:="", Lookat:=xlPart
    .AutoFilter Field:=13, Criteria1:="FP", Operator:=xlOr, Criteria2:="BK"
    dVal = .Worksheet.Evaluate(Replace("AGGREGATE(14,6," & .Columns(3).Address & "/((#=""FP"")+(#=""BK"")),8)", "#", .Columns(13).Address))
    .AutoFilter Field:=3, Criteria1:=">=" & dVal
  End With
End Sub

This is what I ended up with from that earlier sample data (I've hidden the other columns to keep the mini sheet small)

bored622.xlsm
	C										M
6	DPCI										Pull Mode
7	97110681										FP
8	31110815										FP
9	31110851										FP
10	31111021										FP
14	31110297										BK
15	31110480										BK
16	31110676										BK
17	31110678										BK
26
WaveFile

Worked perfectly. thank you

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.