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]

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.

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]

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?

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

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]

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.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
6	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5	Hdr6	Hdr7	Hdr8	Hdr9	Hdr10	Hdr11	Hdr12	Hdr13
7			90										AA
8			45										FP
9			19										FP
10			46										FP
11			78										FP
12			46										FP
13			62										FP
14			75										AA
15			88										BK
16			22										BK
17			17										BK
18			74										BK
19			43										BK
20			36										BK
21			45										BK
22			58										BK
WaveFile

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

bored622.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
6	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5	Hdr6	Hdr7	Hdr8	Hdr9	Hdr10	Hdr11	Hdr12	Hdr13
8			45										FP
10			46										FP
11			78										FP
12			46										FP
13			62										FP
15			88										BK
18			74										BK
21			45										BK
22			58										BK
WaveFile

 

[bored622]

Here is a small amount of data

wave create 2.0 - Copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
6	Distro	Origin Code	DPCI	Request Type	Distro Status	SSZ	Unit Sortable	Tray Code	Total Number of Pallets	Total Number of Full VCPs	Total Number of Loose SSP	SSPs in VCP	Pull Mode	Total Number of Store	Total Number of SSPs in the Distro	VCP Length	VCP Width	VCP Height	Allocation Request Date	Allocation Request Time	WorkStation	Aisle
7	000000000071330489	W	097-11-0681	BAS	N	015	Y	1	0	0	1	12	FP	1	1	15.3	9	5.1	08/23/2022	02:11:25 AM	SF03	162
8	000000000071330494	W	031-11-0815	BAS	N	015	Y	1	0	0	1	12	FP	1	1	13.1	11	6.8	08/23/2022	02:11:29 AM	CR03	111
9	000000000071330497	W	031-11-0851	BAS	N	015	Y	1	0	0	3	12	FP	2	3	15.5	14.5	6.5	08/23/2022	02:11:29 AM	PT01	358
10	000000000071330502	W	031-11-1021	BAS	N	015	Y	1	0	0	1	12	FP	1	1	13.6	9.4	4.3	08/23/2022	02:11:35 AM	SF20	699
11	000000000071330444	W	031-11-0196	BAS	N	015	Y	1	0	0	5	12	FP	4	5	9.2	7.5	4.3	08/23/2022	02:11:33 AM	SF03	140
12	000000000071330449	W	031-11-0257	BAS	N	015	Y	1	0	0	1	12	FP	1	1	9	7	5.5	08/23/2022	02:11:44 AM	SF04	460
13	000000000071330453	W	031-11-0274	BAS	N	015	Y	1	0	0	4	12	BK	2	4	9	7	4.7	08/23/2022	02:11:29 AM	SF03	106
14	000000000071330458	W	031-11-0297	BAS	N	015	Y	1	0	0	1	12	BK	1	1	9	7	4.7	08/23/2022	02:11:25 AM	SF03	155
15	000000000071330476	W	031-11-0480	BAS	N	015	Y	1	0	0	4	12	BK	3	4	8.8	6.9	6.8	08/23/2022	02:11:40 AM	SF21	615
16	000000000071330486	W	031-11-0676	BAS	N	015	Y	1	0	0	3	12	BK	3	3	15.3	9	5.1	08/23/2022	02:11:29 AM	SF03	136
17	000000000071330487	W	031-11-0678	BAS	N	015	Y	1	0	0	2	12	BK	2	2	16.5	10.2	5.1	08/23/2022	02:11:29 AM	SF03	124
18	000000000071326953	W	031-11-0163	BAS	N	015	Y	1	0	1	0	12	BK	8	12	9	7	3.9	08/23/2022	02:11:05 AM	SF03	122
19	000000000071326955	W	031-11-0165	BAS	N	015	Y	1	0	0	8	12	BK	8	8	9	7	3.9	08/23/2022	02:11:05 AM	SF03	122
20	000000000071326957	W	031-11-0167	BAS	N	015	Y	1	0	0	4	12	CA	3	4	9	7	4.7	08/23/2022	02:11:05 AM	SF21	611
21	000000000071326975	W	031-11-0213	BAS	N	015	Y	1	0	1	0	12	CA	11	12	9	7	3.9	08/23/2022	02:11:05 AM	SF21	611
22	000000000071326983	W	031-11-0230	BAS	N	015	Y	1	0	0	9	12	CA	6	9	9	7	3.9	08/23/2022	02:11:05 AM	SF03	140
23	000000000071326984	W	031-11-0231	BAS	N	015	Y	1	0	0	5	12	CA	3	5	9	7	3.9	08/23/2022	02:11:05 AM	CR03	441
24	000000000071326990	W	031-11-0236	BAS	N	015	Y	1	0	0	1	12	CA	1	1	9	7	3.9	08/23/2022	02:11:05 AM	SF20	702
25	000000000071326991	W	031-11-0237	BAS	N	015	Y	1	0	0	6	12	CA	5	6	9	7	3.9	08/23/2022	02:11:05 AM	SF02	238
WaveFile

 

[Peter_SSs]

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]

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]

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]

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]

I was not aware that the file was all text.

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?