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   amp  k  Value                           If tmp   tmpstr Then                               tmp   ActiveSheet Range  B   amp  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

User · Accepted Answer

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

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)

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

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

Examples related to excel

Examples related to vba