CODEWITHSUNDEEP
excelvbacriteriaautofilter
user3050917
user3050917

Reputation: 13

Excel VBA Autofilter issues with multiple inputs per column

I’m trying to write code to auto-filter a column of ‘Job Codes’, which is a three digit number. The idea is to enable the user to enter the specific job code they are looking for, and the macro will return all rows with the corresponding job code. I have run into issues when there are multiple job codes input in the column (some columns may just have one job code, whereas other will have 2 or more). Specifically, with the macro I wrote, it either returns only the rows which contain a single job code in it, or if I write the job code as XXX in the search, it returns only the rows which contain multiple Job Codes – I can never get it to return both cases. Please see below for the code I’m using:

Dim JobCode As String

JobCode = InputBox(prompt:="Which Job Code?", Title:="Job Code")

ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=6, Criteria1:="=*JobCode*", Operator:=xlOr, Criteria2:=JobCode

Any advice would be greatly appreciated. Thanks

Upvotes: 1

Views: 641

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

When you put something between " then it becomes a string and JobCode is a variable.

Try this (TRIED AND TESTED).

JobCode = InputBox(prompt:="Which Job Code?", Title:="Job Code")

ActiveSheet.Range("A1:F7").AutoFilter Field:=6, _
Criteria1:="=*" & JobCode & "*", Operator:=xlOr, Criteria2:=JobCode

ScreenShot:

enter image description here

Upvotes: 1

Related Questions