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



## bored622 (Dec 18, 2022)

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 (Dec 18, 2022)

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time. 😊



bored622 said:


> and filters the top eight from column C.


I presume that means top 8 of the values *still showing after the first filter*?

Assuming that there will be at least 8 still showing after the first filter, would this suffice to produce those results?


```
Sub Filterfp_v2()
  Dim dVal As Double

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


----------



## bored622 (Dec 18, 2022)

Peter_SSs said:


> When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time. 😊
> 
> 
> I presume that means top 8 of the values *still showing after the first filter*?
> ...


Thank you for making my code look better for the post. 

I tried running it, but I get a run-time error of '13'
type mismatch


----------



## Peter_SSs (Dec 18, 2022)

bored622 said:


> but I get a run-time error of '13'
> type mismatch


Whenever you get a vba error, as well as giving the full error message, please make it clear which line of the code the error occurred on.

Could we have a small set of dummy sample data (with XL2BB) that causes that error?

This is what I tested with. The green cells I manually coloured as I thought they were the rows that you would want displayed at the end of the code.

bored622.xlsmABCDEFGHIJKLM6Hdr1Hdr2Hdr3Hdr4Hdr5Hdr6Hdr7Hdr8Hdr9Hdr10Hdr11Hdr12Hdr13790AA845FP919FP1046FP1178FP1246FP1362FP1475AA1588BK1622BK1717BK1874BK1943BK2036BK2145BK2258BKWaveFile

And here is that same worksheet after the code was run.

bored622.xlsmABCDEFGHIJKLM6Hdr1Hdr2Hdr3Hdr4Hdr5Hdr6Hdr7Hdr8Hdr9Hdr10Hdr11Hdr12Hdr13845FP1046FP1178FP1246FP1362FP1588BK1874BK2145BK2258BKWaveFile


----------



## bored622 (Dec 18, 2022)

Here is a small amount of data

wave create 2.0 - Copy.xlsmABCDEFGHIJKLMNOPQRSTUV6DistroOrigin 
CodeDPCIRequest 
TypeDistro 
StatusSSZUnit 
SortableTray 
CodeTotal 
Number
of PalletsTotal 
Number of
Full VCPsTotal
 Number of
Loose SSPSSPs in
VCPPull 
ModeTotal 
Number 
of StoreTotal Number
of SSPs in
the DistroVCP
LengthVCP
WidthVCP
HeightAllocation
Request
DateAllocation
Request TimeWorkStationAisle7000000000071330489W097-11-0681BASN015Y100112FP1115.395.108/23/202202:11:25 AMSF031628000000000071330494W031-11-0815BASN015Y100112FP1113.1116.808/23/202202:11:29 AMCR031119000000000071330497W031-11-0851BASN015Y100312FP2315.514.56.508/23/202202:11:29 AMPT0135810000000000071330502W031-11-1021BASN015Y100112FP1113.69.44.308/23/202202:11:35 AMSF2069911000000000071330444W031-11-0196BASN015Y100512FP459.27.54.308/23/202202:11:33 AMSF0314012000000000071330449W031-11-0257BASN015Y100112FP11975.508/23/202202:11:44 AMSF0446013000000000071330453W031-11-0274BASN015Y100412BK24974.708/23/202202:11:29 AMSF0310614000000000071330458W031-11-0297BASN015Y100112BK11974.708/23/202202:11:25 AMSF0315515000000000071330476W031-11-0480BASN015Y100412BK348.86.96.808/23/202202:11:40 AMSF2161516000000000071330486W031-11-0676BASN015Y100312BK3315.395.108/23/202202:11:29 AMSF0313617000000000071330487W031-11-0678BASN015Y100212BK2216.510.25.108/23/202202:11:29 AMSF0312418000000000071326953W031-11-0163BASN015Y101012BK812973.908/23/202202:11:05 AMSF0312219000000000071326955W031-11-0165BASN015Y100812BK88973.908/23/202202:11:05 AMSF0312220000000000071326957W031-11-0167BASN015Y100412CA34974.708/23/202202:11:05 AMSF2161121000000000071326975W031-11-0213BASN015Y101012CA1112973.908/23/202202:11:05 AMSF2161122000000000071326983W031-11-0230BASN015Y100912CA69973.908/23/202202:11:05 AMSF0314023000000000071326984W031-11-0231BASN015Y100512CA35973.908/23/202202:11:05 AMCR0344124000000000071326990W031-11-0236BASN015Y100112CA11973.908/23/202202:11:05 AMSF2070225000000000071326991W031-11-0237BASN015Y100612CA56973.908/23/202202:11:05 AMSF02238WaveFile


----------



## Peter_SSs (Dec 18, 2022)

bored622 said:


> Here is a small amount of data


Thanks. Your original question was for top 8 in column C. Here, your column C is not numerical, so how could we ascertain "top 8"?


----------



## bored622 (Dec 18, 2022)

Peter_SSs said:


> Thanks. Your original question was for top 8 in column C. Here, your column C is not numerical, so how could we ascertain "top 8"?


I put the wrong file up. Is there a way to remove my post? I meant to place blank cells and just leave data in columns C and M


----------



## Peter_SSs (Dec 18, 2022)

bored622 said:


> I put the wrong file up. There a way to remove my post?


There doesn't appear to be anything sensitive in it, so just go again with the correct sample.


----------



## bored622 (Dec 18, 2022)

Peter_SSs said:


> Thanks. Your original question was for top 8 in column C. Here, your column C is not numerical, so how could we ascertain "top 8"?


I was not aware that the file was all text.


----------



## bored622 (Dec 18, 2022)

bored622 said:


> I was not aware that the file was all text.





Peter_SSs said:


> There doesn't appear to be anything sensitive in it, so just go again with the correct sample.


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


----------



## bored622 (Dec 18, 2022)

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 (Dec 18, 2022)

bored622 said:


> 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.xlsmCM6DPCIPull 
Mode797110681FP831110815FP931110851FP1031111021FP1431110297BK1531110480BK1631110676BK1731110678BK26WaveFile


----------



## bored622 (Dec 18, 2022)

Peter_SSs said:


> Try this
> 
> 
> ```
> ...


Worked perfectly. thank you


----------



## Peter_SSs (Dec 18, 2022)

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


----------

