vba InStr 搜索和单元格中的逗号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15956999/
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
InStr search and commas in a cell
提问by keeg
I have a bunch of column of rows that contain text such as:
我有一堆包含文本的行列，例如：
dog,cat,mouse
bat,dog,fly
fish,beaver,horse
I'm trying to search and highlight rows that contain certain word:
我正在尝试搜索并突出显示包含特定单词的行：
Public Sub MarkDuplicates()
Dim iWarnColor As Integer
Dim rng As Range
Dim rngCell As Variant
Dim LR As Long
Dim vVal
Dim tRow
LR = Cells(Rows.Count, "B").End(xlUp).Row
Set rng = Range("B1:B" & LR)
iWarnColor = xlThemeColorAccent2
For Each rngCell In rng.Cells
tRow = rngCell.Row
If InStr(rngCell.Value, "dog") = 1 Then
rngCell.Interior.ColorIndex = iWarnColor
Else
rngCell.Interior.Pattern = xlNone
End If
Next
End Sub
结束子
This works fine so long as the word 'dog' is the first word in the comma string, so it would highlight the first row but not row two because the word 'dog' appears after 'bat'. Do I need to strip the commas out first or is there a better way of doing this?
只要“狗”这个词是逗号字符串中的第一个词，这个方法就可以正常工作，所以它会突出显示第一行而不是第二行，因为“狗”这个词出现在“蝙蝠”之后。我需要先去掉逗号还是有更好的方法来做到这一点？
回答by PowerUser
It looks like your ultimate goal is to color the row based on whether or not 'dog' is in a cell. Here's a different way to do it that doesn't even involve VBA (this example assumes your data is all in column A):
看起来您的最终目标是根据“狗”是否在单元格中为行着色。这是一种甚至不涉及 VBA 的不同方法（此示例假定您的数据都在 A 列中）：
- Make a new column to the right. Use the formula
=IF(NOT(ISERROR(FIND("dog",A1))),1,0). You can hide the column later so the user doesn't see it. Basically, if it has the word 'dog' somewhere, then return 1 else 0. - Select the entire first row
- Under Conditional Formatting, go to New Rule
- Choose Use a Formula
- For your formula, try
=$B2=1 - Now that you've conditionally colored one row, copy and paste formatto the other rows.
- 在右侧创建一个新列。使用公式
=IF(NOT(ISERROR(FIND("dog",A1))),1,0)。您可以稍后隐藏该列，以便用户看不到它。基本上，如果它在某处有“狗”这个词，则返回 1 否则返回 0。 - 选择整个第一行
- 在条件格式下，转到新规则
- 选择使用公式
- 对于您的公式，请尝试
=$B2=1 - 既然您已经有条件地为一行着色，请将格式复制并粘贴到其他行。
All rows should now update automatically.
所有行现在应该自动更新。
Extra Credit: If this data is formatted as a table object, the conditional formatting should automatically carry over to new rows as they are added.
加分：如果此数据被格式化为表对象，则条件格式应在添加新行时自动转移到新行。
回答by Siddharth Rout
Further to my comments above
进一步我上面的评论
Example 1(Using .Findand .Findnext)
示例 1（使用.Find和.Findnext）
Option Explicit
Public Sub MarkDuplicates()
Dim ws As Worksheet
Dim iWarnColor As Integer
Dim rng As Range, aCell As Range, bCell As Range
Dim LR As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
iWarnColor = xlThemeColorAccent2
With ws
LR = .Range("B" & .Rows.Count).End(xlUp).Row
Set rng = .Range("B1:B" & LR)
rng.Interior.ColorIndex = xlNone
Set aCell = rng.Find(What:="dog", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
Set bCell = aCell
aCell.Interior.ColorIndex = iWarnColor
Do
Set aCell = rng.FindNext(After:=aCell)
If Not aCell Is Nothing Then
If aCell.Address = bCell.Address Then Exit Do
aCell.Interior.ColorIndex = iWarnColor
Else
Exit Do
End If
Loop
End If
End With
End Sub
Screenshot
截屏
Example 2(Using Autofilter)
示例 2（使用自动过滤器）
For this ensure that there is a Heading in Cell B1
为此，请确保 Cell 中有一个 Heading B1
Option Explicit
Public Sub MarkDuplicates()
Dim ws As Worksheet
Dim iWarnColor As Integer
Dim rng As Range, aCell As Range
Dim LR As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
iWarnColor = xlThemeColorAccent2
With ws
'~~> Remove any filters
.AutoFilterMode = False
LR = .Range("B" & .Rows.Count).End(xlUp).Row
Set rng = .Range("B1:B" & LR)
With rng
.AutoFilter Field:=1, Criteria1:="=*dog*"
Set aCell = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
End With
If Not aCell Is Nothing Then aCell.Interior.ColorIndex = iWarnColor
'~~> Remove any filters
.AutoFilterMode = False
End With
End Sub
