## .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 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:

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

…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 SearchString As String, FoundAt As String On Error GoTo Whoa 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 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 Exit Do End If Loop Else MsgBox SearchString & " not Found" Exit Sub End If MsgBox "The Search String has been found these locations: " & FoundAt Exit Sub Whoa: 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 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 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 Exit Do 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 Set oRange = ListRange.Find(what:=FirstRange.Value, LookIn:=xlValues, _ lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not oRange Is Nothing Then Set bCell = oRange: Set aCell = oRange Do 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 Exit Do 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 Set oRange = ListRange.Find(what:=FirstRange.Value, LookIn:=xlValues, _ lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not oRange Is Nothing Then Set bCell = oRange: Set aCell = oRange Do 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 Exit Do End If Loop FindRange = aCell.Address Else FindRange = "Not Found" End If End Function

Sweet code…very nice example.

Thanks :)

Hey, this is quite useful link. Thank you. I’m using double Find (your last section) in my code but it not correctly working formy data. Specially when the find string in B table is part of a list of values (e.g. T1, T2, T8).

Read from this range –

A1~~> T123

A2~~> T362

A3~~> T173

A4~~> T193

Find all occurences from this range and get corresponding values –

B1~~> T123 OUT123X

B2~~> T362, T173 OUT362Y

B3~~> T173, T123 OUT173O

B4~~> T193 OUT123N

B5~~> T123, T166 OUT123N

Required output –

A1~~> T123 OUT123X, OUT173O, OUT123N

A2~~> T362 OUT362Y

A3~~> T173 OUT362Y, OUT173O

A4~~> T193 OUT123N

Can you suggest some solution?

Hello Rajni, You have to use lookat:=xlPart to look for strings embedded inside other strings.

Thank you so much. It worked for me!

I was struggling with this for past few days. On your page ‘only’ I got to know the problem of FindNext usage in user defined function. I was using Find/FindNext earlier; then I tried with Find instead of FindNext.

‘Reply’ button is not available below your response..so responding from this post. Thanks again.

No worries :) Glad it worked :)

Thanks Sid…this is an excellent resource..How did you learn Excel ?

Thanks :) On my own :) Web is an excellent resource.

Siddharth: What about if you had to items such as QP9 and QP9S in tow separate rows. This method will return both rows, regardless of search parameters. Any idea how to make it an exact match to find text entered? I have tried everything without sucess (except looping).

James you will only find a match in QP9S for QP9 if you define you parameter LookAt:=xlPart. To find an exact match use LookAt:=xlWhole

Siddarth has recommended me to post this question to stackoverflow anyway. There he and others perfectly answered my question :)

The link to there is: http://stackoverflow.com/questions/11978184/use-findnext-to-fill-multidimensional-array-vba-excel

You helped my with the following code on stackoverflow. Can I use the .Find in Section 4 to do the same thing. Thanks

Sub check()

Dim i As Long

‘~~> From Row 5 to row 10

‘~~> Chnage as applicable

For i = 5 To 10

Sheets(“Sheet1″).Range(“F” & i).Formula = _

“=VLookup((CONCATENATE(C1,”” “”,C” & i & _

“)),’C:Documents[TestData.xlsx]Sheet1’!$A$2:$G$28,7, FALSE)”

Sheets(“Sheet1″).Range(“F” & i).Value = Sheets(“Sheet1″).Range(“F” & i).Value

Next i

End Sub

Fantastic work .. thanks you so much for this :)

“.FindNext doesn’t work in a User-Defined Function as expected.” Oh thank you microsoft for pointing this out in the help files! I spent two days trying to get .FindNext to produce expected data in a user-defined function. Reasons like this are why I prefer community driven languages! Thanks Siddharth.