vba 如何使用VBA在excel中的单列（A）中搜索字符串

How to search a string in a single column (A) in excel using VBA

提问by confusedMind

I want to change these lines in my excel VBA code to something much faster, instead of looping through all the rows, i did saw examples but could not understand them as i am not a VBA user.

我想将我的 excel VBA 代码中的这些行更改为更快的内容，而不是遍历所有行，我确实看到了示例，但无法理解它们，因为我不是 VBA 用户。

When I used the code in samples (google,this site) I don't see the proper need I want, I want to search column A and if values found return the values in column B next to the searched values, else return empty.

当我在示例（谷歌，本网站）中使用代码时，我没有看到我想要的正确需求，我想搜索 A 列，如果找到值，则返回 B 列中搜索值旁边的值，否则返回空。

Most of the code I used returned error when not found and some other mysterious behavior.

我使用的大多数代码在未找到时返回错误和其他一些神秘行为。

My current code to search is:

我当前要搜索的代码是：

Dim k As Integer
For k = 2 To sheet2Counter - 1                      
    Dim tmp As String                      

    tmp = ActiveSheet.Range("A" & k).Value                      
    If tmp = tmpstr Then                      
        tmp = ActiveSheet.Range("B" & k).Value                      
        tmp = Replace(tmp, "Q", "A")                      
        mainstringtopaste = mainstringtopaste + tmp + ","

            Exit For                      
    End If                    
Next k

Also let me know if this is a better way or any code that will replace it to be more fast.

也让我知道这是否是一种更好的方法或任何可以替代它以更快的代码。

Columns in the sheet to be searched are like:

要搜索的工作表中的列如下所示：

ColumnA        ColumnB
trees          leaves
oranges        fruits
pineapple      fruits
leaves         trees

So as my above code, trees should be searched and leaves should be returned...

所以作为我上面的代码，应该搜索树木并返回叶子......

Thank you

谢谢

回答by brettdj

Below are two methods that are superior to looping. Both handle a "no-find" case.

下面是两种优于循环的方法。两者都处理“找不到”的情况。

1. The VBA equivalent of a normal function VLOOKUPwith error-handling if the variable doesn't exist (INDEX/MATCHmay be a better route than VLOOKUP, ie if your two columns A and B were in reverse order, or were far apart)

2. VBAs FINDmethod (matching a whole string in column A given I use the xlWholeargument)

   Sub Method1()
   Dim strSearch As String
   Dim strOut As String
   Dim bFailed As Boolean
   
   strSearch = "trees"
   
   On Error Resume Next
   strOut = Application.WorksheetFunction.VLookup(strSearch, Range("A:B"), 2, False)
   If Err.Number <> 0 Then bFailed = True
   On Error GoTo 0
   
   If Not bFailed Then
   MsgBox "corresponding value is " & vbNewLine & strOut
   Else
   MsgBox strSearch & " not found"
   End If
   End Sub
   
   Sub Method2()
       Dim rng1 As Range
       Dim strSearch As String
       strSearch = "trees"
       Set rng1 = Range("A:A").Find(strSearch, , xlValues, xlWhole)
       If Not rng1 Is Nothing Then
           MsgBox "Find has matched " & strSearch & vbNewLine & "corresponding cell is " & rng1.Offset(0, 1)
       Else
           MsgBox strSearch & " not found"
       End If
   End Sub
   

1. VLOOKUP如果变量不存在，则VBA 等效于具有错误处理功能的普通函数（INDEX/MATCH可能是比 更好的路线VLOOKUP，即如果您的两列 A 和 B 的顺序相反，或者相距很远）

2. VBAsFIND方法（在给定我使用xlWhole参数的情况下匹配 A 列中的整个字符串）

   Sub Method1()
   Dim strSearch As String
   Dim strOut As String
   Dim bFailed As Boolean
   
   strSearch = "trees"
   
   On Error Resume Next
   strOut = Application.WorksheetFunction.VLookup(strSearch, Range("A:B"), 2, False)
   If Err.Number <> 0 Then bFailed = True
   On Error GoTo 0
   
   If Not bFailed Then
   MsgBox "corresponding value is " & vbNewLine & strOut
   Else
   MsgBox strSearch & " not found"
   End If
   End Sub
   
   Sub Method2()
       Dim rng1 As Range
       Dim strSearch As String
       strSearch = "trees"
       Set rng1 = Range("A:A").Find(strSearch, , xlValues, xlWhole)
       If Not rng1 Is Nothing Then
           MsgBox "Find has matched " & strSearch & vbNewLine & "corresponding cell is " & rng1.Offset(0, 1)
       Else
           MsgBox strSearch & " not found"
       End If
   End Sub