如果列 AJ 包含“客户”，然后在 B:B 中应用过滤器，否则在 VBA 中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16793316/
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 Column AJ contains "Customer" then Apply Filter in B:B Else In VBA
提问by Ajit Kumar Jena
I have written a small script in VBA. Say I have the data in Excel worksheet (sheet1) and want to apply filter on certain condition. If column AJ:AJcontains the word "CUSTOMER", then apply filter range B:Bor Else Msgbox("Customer NA"). I am currently using the code below, but it is making the Excel freeze. Any help really appreciated.
我用 VBA 写了一个小脚本。假设我有 Excel 工作表 ( sheet1) 中的数据，并希望在特定条件下应用过滤器。如果列AJ:AJ包含单词“CUSTOMER”，则应用过滤器范围B:B或Else Msgbox("Customer NA")。我目前正在使用下面的代码，但它使 Excel 冻结。任何帮助真的很感激。
Sub test()
For i = 1 To 1048576
Range("AJ" & i).Select
ss = Len(ActiveCell.Value)
For j = 1 To ss
dd = StrConv(Mid(ActiveCell.Value, j, 8), vbProperCase)
If dd = "Customer" Then
check = 1
check = 0
Range("AJ1").Activate
Selection.AutoFilter
Selection.End(xlToRight).Select
Range("AJ1").Select
ActiveSheet.Range("$A:$AQ518").AutoFilter Field:=36, Criteria1:= _
"=*Customer*", Operator:=xlAnd
Exit For
check = 0
Else
End If
Next j
If check = 1 Then
Exit For
Else
End If
Next i
End Sub
回答by David Zemens
This is freezing excel because you are doing the comparison about as inefficiently as possible :) by looping over everyrow (1048576) and then every character in every cell in that row.
这是冻结excel，因为您正在通过循环遍历每一行（1048576）然后该行中每个单元格中的每个字符来进行尽可能低效的比较:) 。
Instead, try using the range .Findmethod:
相反，请尝试使用 range.Find方法：
Sub testFind()
Dim rng As Range
Dim rngFound As Range
Set rng = Range("AJ:AJ")
Set rngFound = rng.Find("CUSTOMER")
If rngFound Is Nothing Then
MsgBox "Customer N/A"
Else:
'Apply your filter here
End If
End Sub
