vba 是否可以通过VBA中的内容查找单元格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8428914/
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
Is it possible to find a cell by contents in VBA?
提问by BuZz
I wish to find cells (as a Range) that contain a given text. Is that possible to do ? I'd like to be able to find the first cell matching the text (following the natural order on rows and columns).
我希望找到包含给定文本的单元格（作为范围）。那有可能吗？我希望能够找到与文本匹配的第一个单元格（遵循行和列的自然顺序）。
Then, a more advanced question, could I provide a Regex to do the same ?
然后，一个更高级的问题，我可以提供一个正则表达式来做同样的事情吗？
Thank you guys :)
谢谢你们 ：）
回答by chance
Here is a sample code. Replace Range("A1:D10")with your range, and replace the regular expression pattern ni function RE6 with your own.
这是一个示例代码。替换Range("A1:D10")为您的范围，并将正则表达式模式 ni function RE6 替换为您自己的。
Sub SUB1()
For Each c In Worksheets("Sheet1").Range("A1:D10").Cells
If RE6(c.Value) Then
c.Interior.ColorIndex = 7
END IF
Next
End Sub
Function RE6(strData As String) As String
Dim RE As Object, REMatches As Object
Set RE = CreateObject("vbscript.regexp")
With RE
.MultiLine = False
.Global = False
.IgnoreCase = True
.Pattern = "[0-9][0-9][0-9][0-9][0-9][0-9]"
End With
Set REMatches = RE.Execute(strData)
RE6 = REMatches(0)
End Function
回答by Tony Dallimore
I tried adding this to Jerome's answer but I can't until my change is peer reviewed. 20 hours later nothing has happened so I am adding it as an answer.
我尝试将其添加到 Jerome 的答案中，但直到我的更改经过同行评审后才能添加。20 小时后什么也没发生，所以我将其添加为答案。
Don't forget to tidy the macro recorder's output. Find returns a Range which will be Nothing if the find fails. FindNext will loop unless you include code to stop this. You need something like the following:
不要忘记整理宏记录器的输出。Find 返回一个 Range，如果查找失败，该 Range 将为 Nothing。FindNext 将循环，除非您包含阻止此操作的代码。您需要类似以下内容：
Dim ColFind As Integer
Dim RngFind As Range
Dim AddrFirst As String
Dim RowFind As Integer
With ActiveSheet
' Cells can be replaced by any range, for example: Row(5), Columns(3)
' The After cell must be in the search range. It will be the last cell searched.
' Get the With dots on the search range and the after cell to match or
' you will get some very peculiar results.
' What can be, for example: "Th*" to find "The", "There", "Therefore", etc.
' or "Th??" to find "Them", "That", etc.
Set RngFind = .Cells.Find( ... LookIn:=xlValues, After:=.Range("A1"), _
SearchDirection:=xlNext ...)
If RngFind Is Nothing Then
' Code to handle failure
Else
AddrFirst = RngFind.Address
Do
ColFind = RngFind.Column
RowFind = RngFind.Row
' Code to handle found cell
' Use FindPrevious if the Search direction is xlPrevious if
' you want the cells to be found in sequence.
' Warning. I have found that if the Set is omitted, this statement
' overwrites the value of the first cell found with the value
' of the second cell found.
Set RngFind = .Cells.FindNext(RngFind)
Loop While AddrFirst <> RngFind.Address
End If
End With
回答by BuZz
Cells.Find(What:=".GDAXI", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
Cells.FindNext(After:=ActiveCell).Activate
That's a macro recording of what happens when Ctrl F is used, followed by the Find Next feature.
这是使用 Ctrl F 时发生的情况的宏记录，然后是查找下一个功能。
