autofilter results based on cell input

[Tony D]

Hello,
I have a table and would like to know how to filter the results based
on what is entered in a cell

For example from a table below, I want to be able to input in cell A1
"Part123" (no quotes) and the list autofilters all the results. If I
want to autofilter based on the part description, I would like to
enter "Part345" in cell B1 and all filtered results appear.
Any ideas?
Much appreciated for your help!


A B C
100 Part123 Part123 Description Qty
101 Part345 Part345 Description Qty
102 Part678 Part678 Description Qty

 

[Dave Peterson]

You could record a macro when you do it manually:

Show all the data (if you want)
Filter the column that you want using a string
Modify that code to use the cell's value instead of your string
Add a button from the Forms toolbar adjacent to the cell
Assign the macro to this button

Some place in your code, you'll have a line that includes the criteria:

...., Criteria1:="YourString", ...

You'll want to change it to look like:

...., Criteria1:=activesheet.range("A1").value, ...

If/when you need help, just post back with what you have.

 

[Tony D]

You could record a macro when you do it manually:

Show all the data (if you want)
Filter the column that you want using a string
Modify that code to use thecell'svalue instead of your string
Add a button from the Forms toolbar adjacent to thecell
Assign the macro to this button

Some place in your code, you'll have a line that includes the criteria:

..., Criteria1:="YourString", ...

You'll want to change it to look like:

..., Criteria1:=activesheet.range("A1").value, ...

If/when you need help, just post back with what you have.

Hello Dave, I'm afraid that I don't understand. I understand VBA a
little bit and understand recording macros but can't make sense of
what you've written. Thanks so much for responding to my post!

 

[Dave Peterson]

What part did you not understand?

Did you try recording a macro? Post your recorded code and describe what you
tried and how it failed.

 

[Mr. T]

I got it! Thank you so much! Is it possible to still run the
autofilter results without clicking on a form button?

Here was my code:

Sub Column1AutoFilter()

ActiveSheet.Range("$A$2:$B$6").AutoFilter Field:=1,
Criteria1:=Range("A3")

End Sub


Sub Column2AutoFilter()
ActiveSheet.Range("$A$2:$B$6").AutoFilter Field:=2,
Criteria1:=Range("B3")

End Sub

 

[Dave Peterson]

Personally, I would keep the button. I think it's easier for users to
understand and easier to use (fixing typos before clicking the button instead of
processing the typo, for instance).

But there are events that excel monitors. One of these events is the
worksheet_change event.

If you want to try...

Rightclick on the worksheet tab that should have this behavior
Select View Code
Paste this into the code window

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'single cell at a time
If Target.Cells.Count > 1 Then Exit Sub

'only check A1
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub

'tell excel to not look for changes
Application.EnableEvents = False

'your recorded code here
'but change to:
'..., criteria1:=target.value, ...

'tell excel to start looking again
Application.EnableEvents = True

End Sub

You'll have to insert the bulk of your code (not the sub and end sub
statements!) and modify it to use the target.value.

If you have trouble, post back with your code -- the entire code so far!