vba 使用 If 宏查找具有特定文本的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16110311/
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
Using an If macro to find cell with certain text
提问by Jamie Walker
I have a cost sheet with a lot of Macros in it. One of the Macros is to add a new line item to the bottom of the sheet. I want to enter an If code saying that if the item I add starts with the text "Custom " with a space after I want it to find that cell and select it. Below is the code I am trying but it Debugs with Type Mismatch and highlights the Custom = Range("B:B").Value line. Any help is much appreciated.
我有一个成本表，里面有很多宏。其中一个宏是在工作表底部添加一个新的行项目。我想输入一个 If 代码，说明如果我添加的项目以文本“Custom”开头，并在我希望它找到该单元格并选择它之后有一个空格。下面是我正在尝试的代码，但它使用类型不匹配进行调试并突出显示 Custom = Range("B:B").Value 行。任何帮助深表感谢。
Dim Custom As String
Custom = Range("B:B").Value
If Custom Like "Custom *" Then
Cells.Find(What:="Custom ", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.FormulaR1C1 = ("Test")
End If
回答by Siddharth Rout
You do not need to use LIKEfor this. Directly use .FindSee this example
您不需要为此使用LIKE。直接使用.Find看这个例子
Note the use of LookAt:=xlPart. This will ensure that if there is "Custom "anywhere in the cells content, the code will catch it.
注意使用LookAt:=xlPart. 这将确保如果"Custom "单元格内容中有任何地方，代码将捕获它。
Sub Sample()
Dim ws As Worksheet
Dim aCell As Range
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
Set aCell = .Columns(2).Find(What:="Custom ", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
aCell.Value = "Test"
Else
MsgBox "Not Found"
End If
End With
End Sub
