vba 使用 InputBox 过滤一系列数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17878916/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Filtering a range of data using InputBox
提问by 123
I need to filer a dynamic table (about 200 rows/4 columns). I want to enter the filter criteria, in the form of a range, with an InputBox.
我需要过滤一个动态表（大约 200 行/4 列）。我想用一个输入框以范围的形式输入过滤条件。
I need a button that throws an InputBox asking the user to "Enter a range of Serial Numbers" (eg "7-9" and "15-25"), then filter table.("Serial Number" is one the columns of the table.
我需要一个按钮，它会抛出一个 InputBox，要求用户“输入一系列序列号”（例如“7-9”和“15-25”），然后过滤表。（“序列号”是桌子。
回答by NickSlash
Its possible yes, and the Macro Recorder will be able to do most of it for you. Start the recorder, select your data, apply an auto-filter to the id column with a custom filter which allows both >= and <= a value. Then stop the recorder.
它可能是的，并且宏记录器将能够为您完成大部分工作。启动记录器，选择您的数据，使用允许 >= 和 <= 值的自定义过滤器对 id 列应用自动过滤器。然后停止录音机。
You just then need to go into the VBA Editor and modify the macro to take variable input.
然后您需要进入 VBA 编辑器并修改宏以获取变量输入。
Example:
例子：
Macro Output
宏输出
Sub Macro1()
'
' Macro1 Macro
'
Columns("A:C").Select
Selection.AutoFilter
ActiveSheet.Range("$A:$A").AutoFilter Field:=1, Criteria1:=">=5", Operator:=xlAnd, Criteria2:="<=10"
End Sub
Modified Macro
修改宏
Public Sub Macro1m()
Dim A As String
Dim B() As String
Dim Lv As Integer
Dim Hv As Integer
Dim Sv As Integer
A = InputBox("Enter Criteria: ( [low]-[high] )") ' take your input from the user
B = Split(A, "-") ' split the result to get the high and low values
Lv = CInt(B(0)) ' convert the strings to integers
Hv = CInt(B(1)) '
If Lv > Hv Then ' ensure that the high value is > than low value, swapping if needed
Sv = Hv
Hv = Lv
Lv = Sv
End If
Columns("A:C").Select ' original macro code
Selection.AutoFilter
ActiveSheet.Range("$A:$A").AutoFilter Field:=1, Criteria1:=">=" & Lv, Operator:=xlAnd, Criteria2:="<=" & Hv ' macro code modified to use high and low value instead of the constant 5 and 10 entered in the auto-filter configuration
End Sub
