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

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.

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.

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

This question is related to excel vba

The answer is


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

  1. The VBA equivalent of a normal function VLOOKUP with error-handling if the variable doesn't exist (INDEX/MATCH may be a better route than VLOOKUP, ie if your two columns A and B were in reverse order, or were far apart)
  2. VBAs FIND method (matching a whole string in column A given I use the xlWhole argument)

    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