Excel VBA - 在工作表中搜索字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7633656/
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
Excel VBA - Search for string in worksheet
提问by Kenny Bones
I've got this Excel Workbook which I want to search for a specific string in Sheet1. If it finds it, enter a new value. But, the sheet is pretty huge, so I don't think it would be very efficient to loop through each cell. So, can I search through a range and find it that way?
我有这个 Excel 工作簿，我想在 Sheet1 中搜索特定的字符串。如果找到它，请输入一个新值。但是，工作表非常大，所以我认为循环遍历每个单元格不是很有效。那么，我可以搜索一个范围并以这种方式找到它吗？
This is what I've got so far:
这是我到目前为止所得到的：
Dim rngSearchRange as range
With ThisWorkbook.Sheets(1)
Set rngSearchRange = .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row)
End With
The string I'm looking for is the first value in an array of three values. So, how can I search this range for that specific string, find the position and then enter a new value?
我要查找的字符串是包含三个值的数组中的第一个值。那么，如何在此范围内搜索该特定字符串，找到位置然后输入新值？
I was thinking something along this:
我在想一些事情：
Dim rngFindRange as range
Set rngFindRange = rngSearchRange.Find(var(0), LookIn:=xlValues, lookat:=xlWhole)
The var(0) is the value in the array I'm after. But this line doesn't really give me any results. Am I way off here?
var(0) 是我想要的数组中的值。但这行并没有真正给我任何结果。我离这儿很远吗？
采纳答案by brettdj
something like this
像这样的东西
Sub FindMe()
Dim ws As Worksheet
Dim rngSearchRange As Range
Dim rngFindRange As Range
Dim Var
Dim elemVar
Var = Array("apples", "oranges", "fruit")
Set ws = ThisWorkbook.Sheets(1)
Set rngSearchRange = ws.Range(ws.[a2], ws.Cells(Rows.Count, "A").End(xlUp))
For Each elemVar In Var
Set rngFindRange = rngSearchRange.Find(elemVar, LookIn:=xlValues, lookat:=xlWhole)
If Not rngFindRange Is Nothing Then
MsgBox elemVar & " found in " & rngFindRange.Address(0, 0)
Else
MsgBox elemVar & " not found in:"
End If
Next elemVar
End Sub
