I have a worksheet where users can search for all suppliers of a certain item. At the moment they enter their search term in a dialogue box and I then use the advanced filter funtionality to show all rows containing that value. (code below)
However I need to be able to search in more than one column, is there a way to do this?
e.g.
Column A Column B Column C
Supplier 1 - Electrical pumps - inline
Supplier 2 - Impellors - pump spares
Suppplier 3 - valves - hydraulic
If user was to enter 'pump' rows 2 and 3 need to be shown, currently it will only show row 2 as it is filtering on column 2
Any help or sugggestions would be much appreciated
Sub Find_cat()
'
' Find Macro
Dim catvalue
Application.ScreenUpdating = False
catvalue = InputBox("Please enter the item you are searching for", "Category Search")
Sheets("Table").Select
ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("A:s").AutoFilter
ActiveSheet.Range("A$1:$S$987").AutoFilter Field:=8, Criteria1:="*" & catvalue & "*"
End Sub
However I need to be able to search in more than one column, is there a way to do this?
e.g.
Column A Column B Column C
Supplier 1 - Electrical pumps - inline
Supplier 2 - Impellors - pump spares
Suppplier 3 - valves - hydraulic
If user was to enter 'pump' rows 2 and 3 need to be shown, currently it will only show row 2 as it is filtering on column 2
Any help or sugggestions would be much appreciated
Sub Find_cat()
'
' Find Macro
Dim catvalue
Application.ScreenUpdating = False
catvalue = InputBox("Please enter the item you are searching for", "Category Search")
Sheets("Table").Select
ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("A:s").AutoFilter
ActiveSheet.Range("A$1:$S$987").AutoFilter Field:=8, Criteria1:="*" & catvalue & "*"
End Sub