Auto Filter help

kenjohnson_342

Board Regular
Joined
Feb 14, 2007
Messages
67
What I got: worksheet with a bunch of part numbers, descriptions and prices etc that less Excel-Saavy coworkers use all day long.
With 10 columns wide by 10,000 rows deep we use it to quote customers over the phone. I've tried over and over to explain how the "FIND" function works but no go.
I then created a VLOOKUP scenario where they could input the part number to get the info they need, however it has to be an exact match. Sometimes there are suffexes to part numbers that make that difficult.

Wet dream...
I would love to create an input box or a cell, where it could trigger the auto-filter function.
So user enters "CBQ" into the box or cell and it would auto-filter the part column for instances where it contains "CBQ". That would capture all part numbers with or without suffexes.

I have very little experience with programming and writing macros and control boxes. So a complicated vba option won't work well for me. Anybody have any good suggestions for me?

Thanks, Ken.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Have you tried recording a macro while AutoFiltering for contains "CBQ"? That will give you some starter code. You could use the Worksheet_Change event procedure to trigger the AutoFilter.
 

kenjohnson_342

Board Regular
Joined
Feb 14, 2007
Messages
67
Also, if it makes a difference I password protect the worksheet so the rest of the staff can't screw up my pricing and formulas.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

You wouldn't need any controls, just a cell that the user changes.
 

kenjohnson_342

Board Regular
Joined
Feb 14, 2007
Messages
67
ok so I recorded a macro and have the code below so far. Again with very limited experience with macros, how do I take the next step to have it reference the text in cell B1? Thanks for your help so far.

Sub TestLookup()
'
' TestLookup Macro
'

'
ActiveSheet.Range("$A$8:$L$8948").AutoFilter Field:=1, Criteria1:="=*cbq*" _
, Operator:=xlAnd
End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Try this in the module for the worksheet:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$B$1" Then Exit Sub
    Me.Unprotect
    Range("$A$8:$L$8948").AutoFilter Field:=1, Criteria1:="=*" & Target.Value & "*", Operator:=xlAnd
    Me.Protect
End Sub
 

kenjohnson_342

Board Regular
Joined
Feb 14, 2007
Messages
67
Thanks for your help Andrew but there's something I'm not getting.
It's not working for me. When I replace my text with yours my macro disappears completely. I've tried to play with it a little and just can't keep from getting errors.
As good as I feel I am with Excel, I really don't have the chance to learn VBA which is unfortunate.

I think maybe this is beyond what I"m going to be able to do. I think it would've been a really great feature to this file though.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Right click the sheet tab and choose View Code. Copy my code from the Board and paste it into the window on the right. Return to you worksheet and try it out by changing what's in B1.
 

kenjohnson_342

Board Regular
Joined
Feb 14, 2007
Messages
67
**** you're awesome! Followed your instructions and works great.... if I unprotect my sheet. It runs just like I dreamt!

If my sheet is protected and i change the cell B1, it prompts me to enter a password though. How do get by this?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,547
Messages
5,596,782
Members
414,101
Latest member
ExcelBasicBro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top