vba 如果过滤器中没有值，则退出 sub
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21616522/
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
If there is no value in a filter then exit sub
提问by user3281827
I am running a macro where it filters based on a number that the user has entered. It then takes that number and filters a list based on that value. I need a validation where if a user put in a number that is not on the list it will exit the macro. I think I am close but I am new to VBA so I keep getting stuck. Any help would be awesome!
我正在运行一个宏，它根据用户输入的数字进行过滤。然后获取该数字并根据该值过滤列表。我需要一个验证，如果用户输入一个不在列表中的数字，它将退出宏。我想我很接近，但我是 VBA 的新手，所以我一直被卡住。任何帮助都是极好的！
Thanks!
谢谢！
'Filter based on user entry
Sheets("New Revision ").Select
part = Range("B4").Value
Sheets("PN_List").Select
Columns("D:E").Select
Selection.EntireColumn.Hidden = False
ActiveSheet.Range("$A:$K00").AutoFilter Field:=1, Criteria1:=part
If ActiveSheet.Range("$A:$K00").AutoFilter Field=1, Criteria1="" Then
MsgBox "Part number not found please try again.", vbOKOnly + vbExclamation, "Entry Error"
Exit Sub
End If
回答by user2140261
Sheets("New Revision ").Select
part = Range("B4").Value
Sheets("PN_List").Select
If Application.Countif([A1:A3000], part) < 1 Then
MsgBox "Part number not found please try again.", vbOKOnly + vbExclamation, "Entry Error"
Exit Sub
End If
Sheets("PN_List").Columns("D:E").EntireColumn.Hidden = False
ActiveSheet.Range("$A:$K00").AutoFilter Field:=1, Criteria1:=part
回答by andy holaday
Try this out:
试试这个：
Sub Parts()
Dim Part As Variant
Dim Found As Boolean
Part = Sheets("New Revision").Range("B4").Value
On Error Resume Next ' If the lookup fails, ignore the error
' the following assumes Part is in Column A
Found = WorksheetFunction.Match(Part, Sheets("PN_List").Range("$A:$A00"), 0) > 0
On Error GoTo 0 ' Resume normal error handling
If Not Found Then
MsgBox "Part number not found please try again.", vbOKOnly + vbExclamation, "Entry Error"
End If
End Sub
Edit: you can still filter if you want that. Maybe a good place to do that is in an Elseclause of If Not Found.
编辑：如果需要，您仍然可以过滤。也许一个很好的地方，这样做是在Else条款If Not Found。
