I know almost nothing about VBA but I pieced together a macro by copying and
pasting. It currently has a line which applies an autofilter to sort out any
values less than 3:
Selection.AutoFilter Field:=4, Criteria1:="=3", Operator:=xlAnd

I would like to change this from always having a criteria of 3 to allowing
the user to input into an input box any number to filter for. Can someone
please provide an example of how to do this?

Something like this should work for you:
Sub Filter_Stuff()
With Sheets("Sheet1")
.Range("A1:A20").AutoFilter Field:=1, Criteria1:=.Range("E1").Value
End With
End Sub

Sub Unfilter()
Cells.Select
Selection.AutoFilter
End Sub

Put your filter criteria in Cell E1. Try it and see how you get along.

HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Rob" wrote:

I know almost nothing about VBA but I pieced together a macro by copying and
pasting. It currently has a line which applies an autofilter to sort out any
values less than 3:
Selection.AutoFilter Field:=4, Criteria1:="=3", Operator:=xlAnd

I would like to change this from always having a criteria of 3 to allowing
the user to input into an input box any number to filter for. Can someone
please provide an example of how to do this?

This allows a user to enter a number, but they cannot designate the
operators of < or .


Dim myNum
myNum = Application. _
InputBox("Enter a number to filter", "Number", Type:=1)
Selection.AutoFilter Field:=4, Criteria1:=myNum, Operator:=xlAnd


This will allow the user to enter a formula using the operators <, , and =.

Dim myNum
myNum = Application. _
InputBox("Enter a number to filter", "Number", Type:=0)
Selection.AutoFilter Field:=4, Criteria1:=myNum, Operator:=xlAnd


"Rob" wrote in message
...
I know almost nothing about VBA but I pieced together a macro by copying
and
pasting. It currently has a line which applies an autofilter to sort out
any
values less than 3:
Selection.AutoFilter Field:=4, Criteria1:="=3", Operator:=xlAnd

I would like to change this from always having a criteria of 3 to allowing
the user to input into an input box any number to filter for. Can someone
please provide an example of how to do this?

Thank you for the answer. I didn't mention that the worksheet which contains
the macro isn't the worksheet which is being filtered. The first line of
code switches to the other spreadsheet with ActiveWindow.ActivateNext. How
can I take the criteria from cell E1 in the worksheet which contains the
macro, and apply it to the other worksheet?

"ryguy7272" wrote:

Something like this should work for you:
Sub Filter_Stuff()
With Sheets("Sheet1")
.Range("A1:A20").AutoFilter Field:=1, Criteria1:=.Range("E1").Value
End With
End Sub

Sub Unfilter()
Cells.Select
Selection.AutoFilter
End Sub

Put your filter criteria in Cell E1. Try it and see how you get along.

HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Rob" wrote:

I know almost nothing about VBA but I pieced together a macro by copying and
pasting. It currently has a line which applies an autofilter to sort out any
values less than 3:
Selection.AutoFilter Field:=4, Criteria1:="=3", Operator:=xlAnd

I would like to change this from always having a criteria of 3 to allowing
the user to input into an input box any number to filter for. Can someone
please provide an example of how to do this?

From Help:
You can use the sum of the allowable values for Type. For example, for an
input box that can accept both text and numbers, set Type to 1 + 2.

Type 0 + type 1 = 1. So should type:=1 accept both formula and number?



Mike F

"JLGWhiz" wrote in message
...
This allows a user to enter a number, but they cannot designate the
operators of < or .


Dim myNum
myNum = Application. _
InputBox("Enter a number to filter", "Number", Type:=1)
Selection.AutoFilter Field:=4, Criteria1:=myNum, Operator:=xlAnd


This will allow the user to enter a formula using the operators <, , and
=.

Dim myNum
myNum = Application. _
InputBox("Enter a number to filter", "Number", Type:=0)
Selection.AutoFilter Field:=4, Criteria1:=myNum, Operator:=xlAnd


"Rob" wrote in message
...
I know almost nothing about VBA but I pieced together a macro by copying
and
pasting. It currently has a line which applies an autofilter to sort out
any
values less than 3:
Selection.AutoFilter Field:=4, Criteria1:="=3", Operator:=xlAnd

I would like to change this from always having a criteria of 3 to
allowing
the user to input into an input box any number to filter for. Can
someone
please provide an example of how to do this?