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

Question

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

Answer 1

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