.Find and .FindNext In Excel VBA

EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/

 

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

Sub Sample()
    Dim oSht As Worksheet
    Dim lastRow As Long, i As Long
    Dim strSearch As String
    Dim t As Long
    t = GetTickCount
    On Error GoTo Err
    Set oSht = Sheets("Sheet1")
    lastRow = oSht.Range("A" & Rows.Count).End(xlUp).Row
    strSearch = "10000"
    For i = 1 To lastRow
        If oSht.Range("A" & i).Value = strSearch Then
            MsgBox "Value Found in Cell " & oSht.Range("A" & i).Address & vbCrLf & _
            "and it took " & GetTickCount - t & " milliseconds"
            Exit Sub
        End If
    Next i
    Exit Sub
Err:
    MsgBox Err.Description
End Sub

This method of looping is not ‘wrong‘ but yes it is very slow as compared to Excel’s inbuilt “.Find” Tool. The above Sub executed in 109 milliseconds on my laptop.

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 follows…

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.

Sub Sample1()
    Dim oSht As Worksheet
    Dim lastRow As Long, i As Long
    Dim strSearch As String
    Dim t As Long
    Dim aCell As Range
    t = GetTickCount
    On Error GoTo Err
    Set oSht = Sheets("Sheet1")
    lastRow = oSht.Range("A" & Rows.Count).End(xlUp).Row
    strSearch = "10000"
    Set aCell = oSht.Range("A1:A" & lastRow).Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If Not aCell Is Nothing Then
        MsgBox "Value Found in Cell " & aCell.Address & vbCrLf & _
        "and it took " & GetTickCount - t & "milliseconds"
    End If
    Exit Sub
Err:
    MsgBox Err.Description
End Sub

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

And 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

Sub Sample2()
    Dim oSht As Worksheet
    Dim lastRow As Long, i As Long
    Dim strSearch As String
    Dim aCell As Range
    On Error GoTo Err
    Set oSht = Sheets("Sheet2")
    lastRow = oSht.Range("A" & Rows.Count).End(xlUp).Row
    strSearch = "MAX"
    Set aCell = oSht.Range("A1:A" & lastRow).Find(What:=strSearch, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If Not aCell Is Nothing Then
        MsgBox "A"
        aCell.Formula = Replace(aCell.Formula, strSearch, "SUM")
    End If
    Exit Sub
Err:
    MsgBox Err.Description
End Sub

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.

Sub Sample()
    Dim oRange As Range, aCell As Range, bCell As Range
    Dim ws As Worksheet
    Dim ExitLoop As Boolean
    Dim SearchString As String, FoundAt As String
    On Error GoTo Err
    Set ws = Worksheets("Sheet3")
    Set oRange = ws.Columns(1) 

    SearchString = "2"
    Set aCell = oRange.Find(What:=SearchString, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
    If Not aCell Is Nothing Then
        Set bCell = aCell
        FoundAt = aCell.Address
        Do While ExitLoop = False
            Set 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
    Else
        MsgBox SearchString & " not Found"
    End If
    MsgBox "The Search String has been found these locations: " & FoundAt
    Exit Sub
Err:
    MsgBox Err.Description
End Sub

Section 4

Making .Find work as Vlookup() formula

Lets Say we have a database (see picture) 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

Sub Sample()
    Dim ws As Worksheet
    Dim DataRange As Range, UpdateRange As Range, aCell As Range, bCell As Range
    On Error GoTo Err
    Set ws = Worksheets("Sheet4")
    Set UpdateRange = ws.Range("B5:B16")
    Set DataRange = ws.Range("J5:J16")
    For Each aCell In UpdateRange
        Set bCell = DataRange.Find(What:=aCell, LookIn:=xlValues, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
        If Not aCell Is Nothing Then
            aCell.Offset(, 1) = bCell.Offset(, 1)
        End If
    Next
    Exit Sub
Err:
    MsgBox Err.Description
End Sub

Section 5

Using .Find and .FindNext in a Used-Defined Function

Here comes the tricky part.

.FindNext doesn’t work in a User-Defined Function as expected. You can use it in a normal function.

Lets Say We have this data in Sheet1

A1~~> Colt

A2~~> Holt

A3~~> Dolt

A4~~> Hello

and in

B1~~> olt

Now if we paste the below code in a module and run it then we will get the expected result as $A$1:$A$3

Sub Test()
    Sample Sheets("Sheet1").Range("B1"), Sheets("Sheet1").Range("A1:A4")
End Sub 

Sub Sample(FirstRange As Range, ListRange As Range)
    Dim aCell As Range, bCell As Range, oRange As Range
    Dim ExitLoop As Boolean
    Set oRange = ListRange.Find(what:=FirstRange.Value, LookIn:=xlValues, _
    lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    ExitLoop = False
    If Not oRange Is Nothing Then
        Set bCell = oRange: Set aCell = oRange 

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

            If Not oRange Is Nothing Then
                If oRange.Address = bCell.Address Then Exit Do
                Set aCell = Union(aCell, oRange)
            Else
                ExitLoop = True
            End If
        Loop
        MsgBox aCell.Address
    Else
        MsgBox "Not Found"
    End If
End Sub

However it would not work as expected if you paste this function in a module and call it from a worksheet as (Say in Cell C1) =FindRange(A1,A1:A5)

The code will only give you the 1st instance of the value found and ignore the rest

And hence the result that you will get is $A$2!!!

Function FindRange(FirstRange As Range, ListRange As Range) As String
    Dim aCell As Range, bCell As Range, oRange As Range
    Dim ExitLoop As Boolean
    Set oRange = ListRange.Find(what:=FirstRange.Value, LookIn:=xlValues, _
    lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    ExitLoop = False
    If Not oRange Is Nothing Then
        Set bCell = oRange: Set aCell = oRange 

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

            If Not oRange Is Nothing Then
                If oRange.Address = bCell.Address Then Exit Do
                Set aCell = Union(aCell, oRange)
            Else
                ExitLoop = True
            End If
        Loop
        FindRange = aCell.Address
    Else
        FindRange = "Not Found"
    End If
End Function

We need to approach this from a different angle.

Instead of using .FindNext we use .Find again till we get the desired result ($A$1:$A$3). See the code below which works

Function FindRange(FirstRange As Range, ListRange As Range) As String
    Dim aCell As Range, bCell As Range, oRange As Range
    Dim ExitLoop As Boolean
    Set oRange = ListRange.Find(what:=FirstRange.Value, LookIn:=xlValues, _
    lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    ExitLoop = False
    If Not oRange Is Nothing Then
        Set bCell = oRange: Set aCell = oRange 

        Do While ExitLoop = False
            Set oRange = ListRange.Find(what:=FirstRange.Value, After:=oRange, LookIn:=xlValues, _
            lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
            If Not oRange Is Nothing Then
                If oRange.Address = bCell.Address Then Exit Do
                Set aCell = Union(aCell, oRange)
            Else
                ExitLoop = True
            End If
        Loop
        FindRange = aCell.Address
    Else
        FindRange = "Not Found"
    End If
End Function

Hope this small article helps

About these ads