vba 循环遍历列，搜索文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19477577/
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
Loop through column, search for text
提问by Byate
I'm working on a CommandButtonmacro that searches a column in another worksheet for a text string and if found, adds the text "found" to a column in the original worksheet. The search text string is defined by the text in two specific cells in the original worksheet.
我正在研究一个CommandButton宏，该宏在另一个工作表中的列中搜索文本字符串，如果找到，则将文本“找到”添加到原始工作表中的列中。搜索文本字符串由原始工作表中两个特定单元格中的文本定义。
I've constructed some working code that finds the text within a range on another worksheet, however it is very slow when dealing with 1000's of rows. How would I convert my code to use loops in this scenario (which I presume is the quickest way to do this) ?
我已经构建了一些在另一个工作表上查找范围内的文本的工作代码，但是在处理 1000 行时它非常慢。在这种情况下，我将如何将我的代码转换为使用循环（我认为这是最快的方法）？
My current code:
我目前的代码：
Private Sub CommandButton1_Click()
On Error Resume Next
Application.ScreenUpdating = False
Dim artist As String
artist = ActiveSheet.Range("C4").Text
Dim title As String
title = ActiveSheet.Range("C5").Text
Dim tick As String
tick = "found"
Dim c As Range
Dim d As Range
For Each c In Sheets("Reperttheitroade").Range("F1:F2000")
For Each d In Sheets("Reperttheitroade").Range("G1:G2000")
If c.Value = artist And d.Value = title Then
Sheets("Dashboard").Range("F4").Value = artist
Sheets("Dashboard").Range("G4").Value = title
Sheets("Dashboard").Range("H4").Value = tick
End If
Next
Next
End Sub
回答by Alex P
Private Sub CommandButton1_Click()
Dim artistFound As Range, titleFound As Range, artist As String, title As String, c As Range, d As Range
artist = ActiveSheet.Range("C4")
title = ActiveSheet.Range("C5")
Set c = Sheets("Reperttheitroade").Range("F1:F2000")
Set d = Sheets("Reperttheitroade").Range("G1:G2000")
Set artistFound = c.Find(What:=artist, After:=c.Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
Set titleFound = d.Find(What:=title, After:=d.Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not artistFound Is Nothing And Not titleFound Is Nothing Then
With Sheets("Dashboard")
.Range("F4").Value = artist
.Range("G4").Value = title
.Range("H4").Value = "found"
End With
End If
End Sub
回答by Gary's Student
Your real problem is the nested loops.
您真正的问题是嵌套循环。
If you require that both artist and title appear on the same row in "Reperttheitroade", then you need scan down the columns only once looking for the pair.
如果您要求艺术家和标题都出现在“曲目”中的同一行，那么您只需向下扫描列一次以查找对。
If you require that artist appears anywhere in column F and title appears anywhere in column G, then you need first scan down column F looking for artist and then scan down column G looking for the title.
如果您要求艺术家出现在 F 列中的任何位置并且标题出现在 G 列中的任何位置，那么您需要先向下扫描 F 列以查找艺术家，然后向下扫描 G 列以查找标题。
In either case, you don't need nested loops.
无论哪种情况，您都不需要嵌套循环。
EDIT#1:
编辑#1：
Based upon your comment:
根据您的评论：
Sub Button1_Click()
artist = ActiveSheet.Range("C4")
title = ActiveSheet.Range("C5")
tick = "Found"
Set c = Sheets("Reperttheitroade").Range("F1:F2000")
Set d = Sheets("Reperttheitroade").Range("G1:G2000")
For Each cc In c
If cc.Value = artist And cc.Offset(0, 1).Value = title Then
Sheets("Dashboard").Range("F4").Value = artist
Sheets("Dashboard").Range("G4").Value = title
Sheets("Dashboard").Range("H4").Value = tick
Exit Sub
End If
Next cc
End Sub
I used Button1_Clicksince I was using a Forms Button for de-bug.
我使用Button1_Click 是因为我使用 Forms Button 进行调试。
