CODEWITHSUNDEEP
excelvba
confusedMind
confusedMind

Reputation: 2653

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

Upvotes: 10

Views: 193025

Answers (1)

brettdj
brettdj

Reputation: 55682

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

Upvotes: 16

Related Questions