在 VBA excel 中使用“如果单元格包含”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27426835/
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 "If cell contains" in VBA excel
提问by Moogle
I'm trying to write a macro where if there is a cell with the word "TOTAL" then it will input a dash in the cell below it. For example:
我正在尝试编写一个宏，如果有一个带有“TOTAL”字样的单元格，那么它将在其下方的单元格中输入一个破折号。例如：
In the case above, I would want a dash in cell F7 (note: there could be any number of columns, so it will always be row 7 but not always column F).
在上面的例子中，我希望在单元格 F7 中有一个破折号（注意：可能有任意数量的列，所以它总是第 7 行但不总是第 F 列）。
I'm currently using this code, but it's not working and I can't figure out why.
我目前正在使用此代码，但它不起作用，我不知道为什么。
Dim celltxt As String
Range("C6").Select
Selection.End(xlToRight).Select
celltxt = Selection.Text
If InStr(1, celltext, "TOTAL") > 0 Then
Range("C7").Select
Selection.End(xlToRight).Select
Selection.Value = "-"
End If
Help would be appreciated. Hopefully I'm not doing something stupid.
帮助将不胜感激。希望我没有做蠢事。
回答by Chrismas007
This will loop through all cells in a given range that you define ("RANGE TO SEARCH")and add dashes at the cell below using the Offset()method. As a best practice in VBA, you should never use the Selectmethod.
这将遍历您定义的给定范围内的所有单元格，("RANGE TO SEARCH")并使用该Offset()方法在下面的单元格中添加破折号。作为 VBA 中的最佳实践，您永远不应该使用该Select方法。
Sub AddDashes()
Dim SrchRng As Range, cel As Range
Set SrchRng = Range("RANGE TO SEARCH")
For Each cel In SrchRng
If InStr(1, cel.Value, "TOTAL") > 0 Then
cel.Offset(1, 0).Value = "-"
End If
Next cel
End Sub
回答by NameIsPete
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Range("C6:ZZ6")) Is Nothing Then
If InStr(UCase(Target.Value), "TOTAL") > 0 Then
Target.Offset(1, 0) = "-"
End If
End If
End Sub
This will allow you to add columns dynamically and automatically insert a dash underneath any columns in the C row after 6 containing case insensitive "Total". Note: If you go past ZZ6, you will need to change the code, but this should get you where you need to go.
这将允许您动态添加列并在包含不区分大小写的“总计”的 6 行之后自动在 C 行中的任何列下方插入一个破折号。注意：如果您通过 ZZ6，您将需要更改代码，但这应该会让您到达您需要去的地方。
回答by Michinio
This does the same, enhanced with CONTAINS:
这也是一样的，通过 CONTAINS 增强：
Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
Dim I As Long
Dim xRet As String
For I = 1 To LookupRange.Columns(1).Cells.Count
If InStr(1, LookupRange.Cells(I, 1), LookupValue) > 0 Then
If xRet = "" Then
xRet = LookupRange.Cells(I, ColumnNumber) & Char
Else
xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
End If
End If
Next
SingleCellExtract = Left(xRet, Len(xRet) - 1)
End Function
回答by Luca
Dim celltxt As String
Range("C6").Select
Selection.End(xlToRight).Select
celltxt = Selection.Text
If InStr(1, celltext, "TOTAL") > 0 Then
Range("C7").Select
Selection.End(xlToRight).Select
Selection.Value = "-"
End If
You declared "celltxt" and used "celltext" in the instr.
您在指令中声明了“celltxt”并使用了“celltext”。
回答by EEM
Requirement:
Find a cell containing the word TOTALthen to enter a dash in the cell below it.
要求：
找到一个包含单词的单元格，TOTAL然后在它下面的单元格中输入一个破折号。
Solution:This solution uses the Findmethod of the Rangeobject, as it seems appropriate to use it rather than brute force (For…Nextloop).
For explanation and details about the method see Range.Find method (Excel)
解决方案：此解决方案使用对象的Find方法Range，因为使用它而不是蛮力（For…Next循环）似乎更合适。有关该方法的说明和详细信息，请参阅Range.Find 方法 (Excel)
Implementation:
In order to provide flexibility the Findmethod is wrapped in this function:
实现：
为了提供灵活性，该Find方法被包装在这个函数中：
Function Range_?Find_Action(sWhat As String, rTrg As Range) As Boolean
Where:sWhat: contains the stringto search forrTrg: is the rangeto be searched
其中：sWhat: 包含string要搜索的rTrg: 是range要搜索的
The function returns Trueif any match is found, otherwise it returns False
True如果找到任何匹配，该函数返回，否则返回False
Additionally, every time the function finds a match it passes the resulting rangeto the procedure Range_Find_Actionto execute the required action, (i.e. "enter a dash in the cell below it"). The "required action"is in a separated procedure to allow for customization and flexibility.
此外，每次函数找到匹配项时，它都会将结果传递range给过程Range_Find_Action以执行所需的操作（即“在其下方的单元格中输入破折号”）。的“所需的行动”是在一个分离的过程，以允许定制和灵活性。
This is how the function is called:
函数是这样调用的：
This test is searching for "total" to show the effect of the MatchCase:=False. The match can be made case sensitive by changing it to MatchCase:=True
此测试正在搜索“总计”以显示MatchCase:=False. 通过将其更改为，可以使匹配区分大小写MatchCase:=True
Sub Range_Find_Action_TEST()
Dim sWhat As String, rTrg As Range
Dim sMsgbdy As String
sWhat = "total" 'String to search for (update as required)
Rem Set rTrg = ThisWorkbook.Worksheets("Sht(0)").UsedRange 'Range to Search (use this to search all used cells)
Set rTrg = ThisWorkbook.Worksheets("Sht(0)").Rows(6) 'Range to Search (update as required)
sMsgbdy = IIf(Range_?Find_Action(sWhat, rTrg), _
"Cells found were updated successfully", _
"No cells were found.")
MsgBox sMsgbdy, vbInformation, "Range_?Find_Action"
End Sub
This is the Findfunction
这是查找功能
Function Range_?Find_Action(sWhat As String, rTrg As Range) As Boolean
Dim rCll As Range, s1st As String
With rTrg
Rem Set First Cell Found
Set rCll = .Find(What:=sWhat, After:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
Rem Validate First Cell
If rCll Is Nothing Then Exit Function
s1st = rCll.Address
Rem Perform Action
Call Range_Find_Action(rCll)
Do
Rem Find Other Cells
Set rCll = .FindNext(After:=rCll)
Rem Validate Cell vs 1st Cell
If rCll.Address <> s1st Then Call Range_Find_Action(rCll)
Loop Until rCll.Address = s1st
End With
Rem Set Results
Range_?Find_Action = True
End Function
This is the Actionprocedure
这是行动程序
Sub Range_Find_Action(rCll)
rCll.Offset(1).Value2 = Chr(167) 'Update as required - Using `§` instead of "-" for visibilty purposes
End Sub
回答by DejaVuSansMono
Is this what you are looking for?
这是你想要的？
If ActiveCell.Value == "Total" Then
ActiveCell.offset(1,0).Value = "-"
End If
Of you could do something like this
你可以做这样的事情
Dim celltxt As String
celltxt = ActiveSheet.Range("C6").Text
If InStr(1, celltxt, "Total") Then
ActiveCell.offset(1,0).Value = "-"
End If
Which is similar to what you have.
这与您拥有的相似。
