用于搜索文本的 VBA 代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12287690/
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
VBA Code to Search Text
提问by user1649932
I want to amend the code below
我想修改下面的代码
I want the findwhat to pick text from a range ("a1:20")-which contains the list of text i want to look for
我想要 findwhat 从一个范围（“a1:20”）中选择文本 - 其中包含我想要查找的文本列表
If it finds the text in the description in B, i want it to return the text it found e.g jo IN range c beside its description
如果它在 B 的描述中找到文本，我希望它返回它找到的文本，例如 jo IN range c 在其描述旁边
Thanks
谢谢
Option Explicit
Sub x()
Dim FindWhat, rngCell As Range, i As Integer
FindWhat = Array("Jo", "oa", "of", "koo")
For i = 0 To 3
For Each rngCell In Range("B2", Range("B" & Rows.Count).End(xlUp))
If InStr(rngCell, FindWhat(i)) <> 0 Then
rngCell.Offset(0, 1) = rngCell
rngCell.Offset(, 1).Resize(, 2).Copy
End If
Next rngCell
Next i
End Sub
回答by mkingston
Try this:
尝试这个：
Option Explicit
Sub x()
Dim findRng As Range, _
targetRng As Range, _
findCell As Range, _
found As Range
Dim i As Integer
Dim firstFound As String, _
columnName As String
columnName = "B"
Set findRng = Range("A1:A20")
For Each findCell In findRng
Set targetRng = Range(columnName & "2", Range(columnName & Rows.Count).End(xlUp))
With targetRng
Set found = .Find(findCell.Value, LookIn:=xlValues, lookat:=xlPart)
If Not found Is Nothing Then
firstFound = found.Address
Do
found.Offset(0, 1).Value = found.Offset(0, 1).Value & findCell.Value & ", "
Set found = .FindNext(found)
Loop While Not found Is Nothing And found.Address <> firstFound
End If
End With
Next findCell
End Sub
Note that unless what you're doing is a little more complex than this, you can achieve this result using cell formulae. Read the help on the Index, Match and VLookup functions to see how to achieve this.
请注意，除非您正在执行的操作比这稍微复杂一些，否则您可以使用单元格公式实现此结果。阅读有关索引、匹配和 VLookup 函数的帮助以了解如何实现这一点。
