How to Find a Value in a Column Using Excel VBA

1. Find a String/Value in a Column Using Find() Function in VBA
2. Find a String/Value in a Column Using Match() Function in VBA
3. Find a String/Value in a Column Using Loops in VBA
4. Finding a String in Multiple Columns Using Loops in VBA

Creating automation tools in Excel requires several string matching functions like Instr(), CStr(), Split(), etc. These functions find a substring within a string. However, when dealing with strings/values through column/s, you can’t use these functions as these are single-stringed functions.

This article will demonstrate three techniques for finding a string or a value in a column. The return value is the row number where the target string is.

String Search Techniques:

1. Using Find() function
2. Using Match function
3. Using Loops

For the whole article, this virtual sheet will be the reference sheet.

Sheet1 values:

  |   A     | B |       C      |  D  |
1 | Apple   | 4 | Philippines  |  3  |
2 | Orange  | 3 | Mexico       |  5  |
3 | Banana  | 6 | Thailand     |  3  |
4 | Carrot  | 7 | Saudi Arabia |  7  |
5 | Grapes  | 5 | U.S.A.       |  1  |
6 | Pear    | 3 | Japan        |  0  |
7 | Citrus  | 9 | Mexico       |  3  |
8 | Pomelo  | 2 | Taiwan       |  4  |
9 |         | 3 | Singapore    |  7  |
10|         | 4 | Mexico       |  8  |

Find a String/Value in a Column Using Find() Function in VBA

Syntax:

Range.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase)

Parameters:

The code block below will demonstrate using the Find() function to return the row of the string to search.

Function SearchStr(str As String) As Range

Dim wb As Workbook
Dim s1 As Worksheet
Dim rng As Range

Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")

Set rng = s1.Columns("A:A")
Set SearchStr = rng.Find(What:=str, LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False)

End Function

Sub BananaSearch()

If SearchStr("Banana") Is Nothing Then
    Debug.Print "Not in range."

Else
    Debug.Print "Found at row: " & SearchStr("Banana").Row

End If

End Sub

Sub MelonSearch()

If SearchStr("Melon") Is Nothing Then
    Debug.Print "Not in range."

Else
    Debug.Print "Found at row: " & SearchStr("Melon").Row

End If
End Sub

BananaSearch Output:

Found at row: 3

MelonSearch Output:

Not in range

Find a String/Value in a Column Using Match() Function in VBA

The main difference between the Find() and Match() functions is that the former returns the Range object where the string is found while the latter returns the position where the string has a match.

Syntax:

Application.Match([StringtoSearch],[RangetoSearchIn],[MatchType])

Parameters:

Remarks:

For [MatchType] arguments, here are the values:

The code block below will demonstrate using the Match() function to return the row of the string to search.

Function SearchNum(IntToSearch As Integer) As Variant

Dim wb As Workbook
Dim s1 As Worksheet
Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")

If Not IsError(Application.Match(IntToSearch, s1.Columns("B:B"), 0)) Then
    SearchNum = Application.Match(IntToSearch, s1.Columns("B:B"), 0)

Else
    SearchNum = "Not Found"
End If

End Function

Sub LookForSix()

Debug.Print "A match is located at row " & SearchNum(6)

End Sub

Sub LookForZero()

Debug.Print "A match is located at row " & SearchNum(0)

End Sub

LookForSix Output:

A match is located at row 3

LookForZero Output:

A match is located at row Not Found

Find a String/Value in a Column Using Loops in VBA

Unlike Find() and Match() functions, utilizing loops allows the user to execute the code. One advantage of loops is that we can return all the matches from a Range. However, loops are not recommended when dealing with large data as it is iterating with each cell within the specified limits.

The code block below will demonstrate utilizing loops to return the row of the string to search.

Function GetAllRows(str As String) As Object

Dim wb As Workbook
Dim s1 As Worksheet
Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")
Dim coll As Object
Set coll = CreateObject("System.Collections.ArrayList")
Dim i As Long
Dim lrow As Long

lrow = s1.Cells(s1.Rows.Count, 3).End(xlUp).Row

For i = 1 To lrow
    
    If s1.Cells(i, 3) = str Then
        'Add row number in the arraylist
        coll.Add (i)
        
    End If
    
Next i

Set GetAllRows = coll

End Function

Sub FindForMexico()

Dim coll2 As Object

Set coll2 = GetAllRows("Mexico")

Dim j As Integer

For j = 0 To coll2.Count - 1

    Debug.Print "A match is found in row: " & coll2(j)

Next j

End Sub

FindforMexico Output:

A match is found in row: 2
A match is found in row: 7
A match is found in row: 10

Finding a String in Multiple Columns Using Loops in VBA

The three code blocks above were designed to look into just one column. Rarely, you need to look for string matches across multiple columns or even within the whole worksheet. But if you need to, the code block below will help you.

Function GetAllRowsFromSheet(str As String) As Object

Dim wb As Workbook
Dim s1 As Worksheet
Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")
Dim coll As Object
Set coll = CreateObject("System.Collections.ArrayList")

'Create row numbers(i,j)  and last row/column variable in long datatypes.
Dim i, j As Long
Dim lrow, lcol As Long

lrow = s1.Cells(s1.Rows.Count, 3).End(xlUp).Row

lcol = s1.Cells(1, s1.Columns.Count).End(xlToLeft).Column

For j = 1 To lcol
    For i = 1 To lrow
        
        If s1.Cells(i, j) = str Then
            coll.Add ("Row :" & i & " Column : " & j)
        End If 
    Next i
Next j

Set GetAllRowsFromSheet = coll

End Function

Sub FindFor3()

Dim coll2 As Object

Set coll2 = GetAllRowsFromSheet(3)

Dim j As Integer

For j = 0 To coll2.Count - 1

Debug.Print "A match is found in " & coll2(j)

Next j

End Sub

Lookfor3 Output:

A match is found in Row :2 Column : 2
A match is found in Row :6 Column : 2
A match is found in Row :9 Column : 2
A match is found in Row :1 Column : 4
A match is found in Row :3 Column : 4
A match is found in Row :7 Column : 4