The most under utilized feature of Excel when it comes to finding data is .Find and .FindNext.

Most of us use loops in case we want to find something in Excel. For Example, Let’s say Our data is in sheet1 from Cell A1 to A65000 and the data is like this

A1 → 1
A2 → 2
A3 → 3
A4 → 4
A5 → 5


A65000 → 65000

Now suppose we want to find which cell has say 10000. The primitive way was to loop through each cell and find which cell had that value. For Example:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects 
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Define variables to store Find 
        Dim ToFind As String = "10000"

        '~~> Open the Excel file
        xlWorkBook = xlApp.Workbooks.Open("C:\Sample.xlsx")

        '~~> Sheet where you want to do a replace
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        '~~> Display Excel
        xlApp.Visible = True

        With xlWorkSheet
            Dim lastRow As Integer = .Range("A" & .Rows.Count).End(Excel.XlDirection.xlUp).Row

            For i As Integer = 1 To lastRow
                If .Range("A" & i).Value = ToFind Then
                    MessageBox.Show("Value Found in Cell " & .Range("A" & i).Address)
                    Exit Sub
                End If
            Next i
        End With
    End Sub
End Class

This method of looping is not wrong but yes it is very slow as compared to Excel’s inbuilt .Find Tool.

In this tutorial, I will stress on how to use .Find to make your search faster.

The syntax of .Find is:

expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Where:

  • Expression (Required): is any valid range Object. So if we take the above example then the range would be Range(“A1:A” & lastRow).
  • What (Optional Variant): is the “Search value”
  • After (Optional Variant): The cell after which you want the search to begin.
  • LookIn (Optional Variant): The type of information. (xlValues or xlFormulas)
  • LookAt (Optional Variant): Can be one of the following XlLookAt (constants): xlWhole or xlPart.
  • SearchOrder (Optional Variant): Can be one of the following XlSearchOrder constants: xlByRows or xlByColumns.
  • SearchDirection: Can be one of these XlSearchDirection constants. xlNext default xlPrevious.
  • MatchCase (Optional Variant): True to make the search case sensitive. The default value is False.
  • MatchByte (Optional Variant): Used only if you’ve selected or installed double-byte language support. True to have double-byte characters match only double-byte characters. False to have double-byte characters match their single-byte equivalents.
  • SearchFormat (Optional Variant): The search format.

Let’s now take different scenarios on how to work with .Find

The other scenarios that we will work on are as follow:

  • 1) Find Values in Cell Value (See Section 1 below)
  • 2) Find Values in Cell Formula (See Section 2 below)
  • 3) .FindNext (See Section 3 below)
  • 4) Making .Find work as Vlookup() formula (See Section 4 below)
  • 5) Using .Find and .FindNext in a Used-Defined Function (See Section 5 below)

Section 1

Now let’s try and incorporate .Find to find the data that we want:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects 
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Define variables to store Find 
        Dim ToFind As String = "10000"
        Dim aCell As Excel.Range = Nothing

        '~~> Open the Excel file
        xlWorkBook = xlApp.Workbooks.Open("C:\Sample.xlsx")

        '~~> Sheet where you want to do a replace
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        '~~> Display Excel
        xlApp.Visible = True

        With xlWorkSheet
            Dim lastRow As Integer = .Range("A" & .Rows.Count).End(Excel.XlDirection.xlUp).Row

            aCell = .Range("A1:A" & lastRow).Find(What:=ToFind, _
                                                  LookIn:=Excel.XlFindLookIn.xlValues, _
                                                  LookAt:=Excel.XlLookAt.xlPart, _
                                                  SearchOrder:=Excel.XlSearchOrder.xlByRows, _
                                                  SearchDirection:=Excel.XlSearchDirection.xlNext, _
                                                  MatchCase:=False, _
                                                  SearchFormat:=False)

            If Not aCell Is Nothing Then MessageBox.Show("Value Found in Cell " & aCell.Address)
        End With
    End Sub
End Class

The above sub took 6 milliseconds as compared to 109 milliseconds on the looping sub !!!

Section 2 – Find Values in Cell Formula

Let’s say our data is as follows:

Quote:

B1 → 1
B2 → 2
B3 → 3
B4 → 4
B5 → 5


B100 → 100

…this

C1 → 1
C2 → 2
C3 → 3
C4 → 4
C5 → 5


C100 ~~> 100

…and finally this:

A1 → =SUM(B1:C1)
A2 → =SUM(B2:C2)
A3 → =SUM(B3:C3)
A4 → =SUM(B4:C4)
A5 → =SUM(B5:C5)


A27 → =MAX(B5:C5)

A100 → =SUM(B2:C2)

Now I want to Find the word MAX in the formula and replace it with say SUM so using .Find we can achieve it in the following manner.

Note that since we are searching for values in the formula then LookIn takes the value of xlFormulas

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects 
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Define variables to store Find 
        Dim ToFind As String = "MAX"
        Dim aCell As Excel.Range = Nothing

        '~~> Open the Excel file
        xlWorkBook = xlApp.Workbooks.Open("C:\Sample.xlsx")

        '~~> Sheet where you want to do a replace
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        '~~> Display Excel
        xlApp.Visible = True

        With xlWorkSheet
            Dim lastRow As Integer = .Range("A" & .Rows.Count).End(Excel.XlDirection.xlUp).Row

            aCell = .Range("A1:A" & lastRow).Find(What:=ToFind, _
                                                  LookIn:=Excel.XlFindLookIn.xlFormulas, _
                                                  LookAt:=Excel.XlLookAt.xlPart, _
                                                  SearchOrder:=Excel.XlSearchOrder.xlByRows, _
                                                  SearchDirection:=Excel.XlSearchDirection.xlNext, _
                                                  MatchCase:=False, _
                                                  SearchFormat:=False)

            If Not aCell Is Nothing Then
                MessageBox.Show("Value Found in Cell " & aCell.Address)
                aCell.Formula = aCell.Formula.ToString.Replace(ToFind, "SUM")
            End If
        End With
    End Sub
End Class

Section 3 – .FindNext

Let’s say our data is as follows

Quote:

A1 ~~> 1
A2 ~~> 2
A3 ~~> 3
A4 ~~> 4
A5 ~~> 5


A27~~> 2


A45~~> 2


A100 ~~> 100

If you have noticed that Cell A2, A27, A45 has the same value which is 2.

So if I want to find all “2” ‘s one after the other then how do I do that using .Find. It is very simple. We use .FindNext in a loop.  See example below.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects 
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Define variables to store Find 
        Dim ToFind As String = "2"
        Dim aCell As Excel.Range = Nothing
        Dim bCell As Excel.Range = Nothing
        Dim ExitLoop As Boolean = False
        Dim FoundAt As String = ""

        '~~> Open the Excel file
        xlWorkBook = xlApp.Workbooks.Open("C:\Sample.xlsx")

        '~~> Sheet where you want to do a replace
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        '~~> Display Excel
        xlApp.Visible = True

        With xlWorkSheet
            Dim lastRow As Integer = .Range("A" & .Rows.Count).End(Excel.XlDirection.xlUp).Row
            Dim oRange As Excel.Range = .Range("A1:A" & lastRow)

            aCell = oRange.Find(What:=ToFind, _
                                LookIn:=Excel.XlFindLookIn.xlValues, _
                                LookAt:=Excel.XlLookAt.xlWhole, _
                                SearchOrder:=Excel.XlSearchOrder.xlByRows, _
                                SearchDirection:=Excel.XlSearchDirection.xlNext, _
                                MatchCase:=False, _
                                SearchFormat:=False)

            If Not aCell Is Nothing Then
                bCell = aCell
                FoundAt = aCell.Address

                Do While ExitLoop = False
                    aCell = oRange.FindNext(After:=aCell)

                    If Not aCell Is Nothing Then
                        If aCell.Address = bCell.Address Then Exit Do
                        FoundAt = FoundAt & ", " & aCell.Address
                    Else
                        ExitLoop = True
                    End If
                Loop

                MessageBox.Show("The Search String has been found these locations: " & FoundAt)
            Else
                MessageBox.Show(ToFind & " not Found")
            End If
        End With
    End Sub
End Class

Section 4 – Making .Find work as Vlookup() formula

Lets Say we have a database and a range where data needs to be updated (see picture):

Now suppose we have to find the capitals for the relevant companies then we can use .Find to get the relevant companies. For Example:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects 
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Define variables to store Find 
        Dim aCell As Excel.Range = Nothing
        Dim bCell As Excel.Range = Nothing

        '~~> Open the Excel file
        xlWorkBook = xlApp.Workbooks.Open("C:\Sample.xlsx")

        '~~> Sheet where you want to do a replace
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        '~~> Display Excel
        xlApp.Visible = True

        With xlWorkSheet
            Dim DataRange As Excel.Range = .Range("B1:B16")
            Dim UpdateRange As Excel.Range = .Range("F4:F7")

            For Each aCell In UpdateRange
                bCell = DataRange.Find(What:=aCell, _
                                       LookIn:=Excel.XlFindLookIn.xlValues, _
                                       LookAt:=Excel.XlLookAt.xlWhole, _
                                       SearchOrder:=Excel.XlSearchOrder.xlByRows, _
                                       SearchDirection:=Excel.XlSearchDirection.xlNext, _
                                       MatchCase:=False, _
                                       SearchFormat:=False)
                If Not bCell Is Nothing Then
                    aCell.Offset(, 1).Value = bCell.Offset(, 1).Value
                End If
            Next
        End With
    End Sub
End Class

Snapshot