Macro that filters a table based on a single Cell's value.

[Kelko]

Is there a way to use a macro that when run will filter a data table based on a fill in the balk cell value? By fill in the blank I mean to reference a cell that's outside of the table, that when a user types in a number, say "R250", that the macro knows to use the input "R250" to filter a filed in my data table? I'm essentially trying to make a simple filed search query with for a single value input. It seems I can't reference when using the filter option under a filed heading, or get a macro to copy and paste a cell's value into the filter function.

 

[My Aswer Is This]

Try this:

Sub Filter_Me()
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=[COLOR="#FF0000"]Cells(1, 10).Value[/COLOR]
End Sub

 

[Kelko]

Try this:

Sub Filter_Me()
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=[COLOR=#ff0000]Cells(1, 10).Value[/COLOR]
End Sub

Thank you for your help.

Is this what the code should look like after inserting my data? I'm a little unsure (a lot rather) about the end the first line
" Criteria=" area.

Code:
Sub SearchMeNow()
Sub Filter_Me()
ActiveSheet.ListObjects("RingGages").Range.AutoFilter Field:=6, Criteria1:=Cells(P, 20).Value
End Sub
'
' SearchMeNow Macro.


The table I'm trying to filter is called "RingGages", and I'm trying to filter filed 6 which contains each gage's individual identification number. I'd like the cell "P20" to be used as a search box so that when a user types in a gage number in "P20", the macro uses the info in cell "P20" to filter the table.

Hope you can help. Thank you again.

 

[My Aswer Is This]

You cannot use:
Criteria1:=Cells(P, 20).Value


You must use:
Criteria1:=Cells(16, 20).Value

You must use numbers not characters.

And why two subs?
Sub SearchMeNow()
Sub Filter_Me()

 

[My Aswer Is This]

Did you create this table and it came out named like this:
ActiveSheet.ListObjects("RingGages").Range.AutoFilter Field:=6, Criteria1:=Cells(P, 20).Value

Most Tables are named Table1 or Table2
If you name your Table "RingeGages" then I think it must look like this:

ActiveSheet.Range("RingeGages").AutoFilter Field:=6 ', Criteria1:=Cells(16, 20).Value

 

[Kelko]

Did you create this table and it came out named like this:
ActiveSheet.ListObjects("RingGages").Range.AutoFilter Field:=6, Criteria1:=Cells(P, 20).Value

Most Tables are named Table1 or Table2
If you name your Table "RingeGages" then I think it must look like this:

ActiveSheet.Range("RingeGages").AutoFilter Field:=6 ', Criteria1:=Cells(16, 20).Value

You are correct, I did name the table " RingGages". What do the 16 and 20 represent? I'm not sure how they work into the code? Total novice here.

As for the two Sub's, I just made a mistake when copying the code back into this thread.

 

[Kelko]

Correction: you were right, the table is a table lol. Its Table4 in my worksheet.

 

[My Aswer Is This]

16 equals Column 16
20 equals row 20
I should have wrote it this way
Cells(20, 16).Value

 

[My Aswer Is This]

Then your script should look like:
ActiveSheet.ListObjects("Table4").Range.....