Excel VBA Find – A Complete Guide

VBA Find

“I know well what I am fleeing from but not what I am in search of” – Michel de Montaigne

 
 

Introduction

This post covers everything you need to know about the VBA Find function. It explains, how to use Find, in simple terms. It also has tons of code examples of Find you can use right now.

If you want to go straight to an example of Find then check out How to do a Simple Find.

If you want to search for text within a string then you are looking for the InStr and InStrRev functions.

If you want to find the last row or column with data then go to Finding the Last Cell Containing Data

 
 

What is the VBA Find Function?

The Find function is very commonly used in VBA. The three most important things to know about Find are:

  1. The Find function is a member of Range.
  2. It searches a range of cells containing a given value or format.
  3. It is essentially the same as using the Find Dialog on an Excel worksheet.

 
 

Excel Find Dialog

To view the Excel Find dialog, go to the Home ribbon and click on Find & Select in the Editing section. In the menu that appears select Find(shortcut is Ctrl + F)

Excel VBA Find Ribbon

 
 
When  you do this the following dialog will appear

Excel Find dialog

 
 
The VBA Find function uses most of the options you can see on this Dialog.

 
 

How to Use Options With Find

To use the options you pass them as parameters to the Find function. This is similar to how you use worksheet functions. For example, the Sum function has a Range as a parameter. This means you give it a range when you use it.

The VBA Find uses parameters in the same way. You must give it the item you are searching for. This is the first parameter and it is required.

The rest of the parameters are optional. If you don’t use them then Find will use the existing settings. We’ll see more about this shortly.

The table in the next section shows these parameters. The sections that follow this, give examples and details of how to use these parameters.

 
 

VBA Find Parameters

The following tables shows all the Find parameters.

ParameterTypeDescriptionValues
WhatRequiredThe value you are searching forAny VBA data type e.g String, Long
AfterOptionalA single cell range that you start your search fromRange("A5")
LookInOptionalWhat to search in e.g. Formulas, Values or CommentsxlValues, xlFormulas, xlComments
LookAtOptionalLook at a part or the whole of the cell xlWhole, xlPart
SearchOrderOptionalThe order to searchxlByRows or xlByColumns.
SearchDirection OptionalThe direction to searchxlNext, xlPrevious
MatchCaseOptionalIf search is case sensitiveTrue or False
MatchByteOptionalUsed for double byte languagesTrue or False
SearchFormatOptionalAllow searching by format. The format is set using Application.FindFormatTrue or False

 
 

Important Note about Find Parameters

Keep the following in mind as it can cause a lot of frustration when using Find.

As you can see from the table most of the VBA Find parameters are optional. As we said earlier, if you don’t set a Find parameter it uses the existing setting.

For example, if you set the LookIn parameter to xlComments, it will search for a value in comments only. The next time you run Find(either from the Dialog or from VBA) the existing LookIn setting will be Comments.

The following code shows an example of this

' Search in comments only
Range("A1:A5").Find "John", LookIn:=xlComments
' Will search comments as this is the existing setting
Range("A1:A5").Find "John"

' Search in formulas only
Range("A1:A5").Find "John", LookIn:=xlFormulas
' Will search formulas as this is the existing setting
Range("A1:A5").Find "John"

 
 
This applies to the parameters LookIn, LookAt, SearchOrder, and MatchByte.

 
 

The Find Return Value

If the search item is found then Find returns  the cell with the value. That is, it returns a Range type of one cell.

If the search item is not found then Find returns an object set to Nothing.

In the following examples, you will see how to deal with the return value.

 
 

How to do a Simple Find

Let’s start with a simple example of the VBA Find. You need three things when using the Find function

  1. The Range to search
  2. The value you are searching for
  3. The Range to store the returned cell

 
 
Let’s take the following sample data

Excel VBA Find

 
 
We are going to search for the text “Jena” in the cells A1 to A5.

The following code searches for “Jena”. When it finds “Jena”, it then places the cell in the rgFound variable.

' Find the name Jena in the range A1:A5
Dim rgFound As Range
Set rgFound = Range("A1:A5").Find("Jena")

' Print cell address to Immediate Window(Ctrl + G)
Debug.Print rgFound.Address

 
 
The above code shows the most basic search you can do. If this is your first time using the VBA Find function then I recommend you practice with a simple example like this.

 
 

When the Value is not Found

When you use the VBA Find function, there will be times when you do not find a match. You need to handle this in your code or you will get the following error when you try to use the returned range

Excel VBA Find

 
 
The following code will give this error if the text “John” is not found in the range A1 to A5

Set rgFound = Range("A1:A5").Find("John")

' Shows Error if John was not found
Debug.Print rgFound.Address

 
 
What we need to do is check the return value like the following code shows

Set rgFound= Range("A1:A5").Find("John")

If rgFound Is Nothing Then
    Debug.Print "Name was not found."
Else
    Debug.Print "Name found in :" & rgFound.Address
End If

 
 

Using After with Find

The After parameter is used if you want to start the search from a particular cell. This is the same as when you do a search with Excel Find Dialog. With the dialog, the active cell is considered the After cell.

 
 

Example 1 Without After

Let’s look at the following code.

Set cell = Range("A1:A6").Find("Rachal")

 
 
Find will return the cell A2 as this is where the first “Rachal” is found.

Excel VBA Find No After

 
 

Example 2 Using After

In the next example, we use after. We are telling VBA to start the search for “Rachal” after cell A2

Set cell = Range("A1:A6").Find("Rachal", After:=Range("A2"))

 
 
This will return the cell A6

Find with After

 
 

Example 3 Wrapping Around

If a match is not found then the search will “wrap around”. This means it will go back to the start of the range.

In the following example, we are looking for Drucilla. We start our search After cell A2. Find will search from A3 to A6 and then will move to A1.

So the following code will return A1 as there is no text “Drucilla” from A3 to A6.

Set cell = Range("A1:A6").Find("Drucilla", After:=Range("A2"))

 
 
VBA Find with After Wrap

 
 
The search order for this example was A4, A5, A6, A1.

 
 

Using LookIn with Find

Using LookIn allows you to search in Values, Formulas or Comments.

Important Note: When a cell has text only, this text is considered a formula AND a value. See the table below for details

Cell ContainsResultLookIn value is
AppleAppleValue and Formula
="App" & "le"'AppleValue only
=LEFT("Apple",4)'ApplFormula only

 
 
We are going to use the following sample data.

A2 Contains “Apple” as a value only
A3 Contains  “Apple” as a formula only
A4 Contains “Apple” in  the comment only

 
 
VBA Find LookIn

 
 
The code below searches for “Apple” in the different types

Sub UseLookIn()

    Dim cell As Range

    ' Finds A2
    Set cell = Range("A1:A4").Find("Apple", LookIn:=xlValues)
    Debug.Print cell.Address

    ' Finds A3
    vSet cell = Range("A1:A4").Find("Apple", LookIn:=xlFormulas)
    Debug.Print cell.Address

    ' Finds A4
    Set cell = Range("A1:A4").Find("Apple", LookIn:=xlComments)
    Debug.Print cell.Address

End Sub

 
 

Using LookAt with Find

Using the LookAt function is pretty straightforward.

  1. xlWhole means the search value must match the entire cell contents.
  2. xlPart means the search value only has to match part of the cell.

 
 
The following example has “Apple” as part of the cell contents in A2 and it is the full contents in cell A3.
VBA Find LookAt

 
 
The first Find in the following code finds “Apple” in A2. The second Find is looking for a full match so finds A3.

Sub UseLookAt()

    Dim cell As Range

    ' Finds A2
    Set cell = Range("A1:A3").Find("Apple", Lookat:=xlPart)
    Debug.Print cell.Address

    ' Finds A3
    Set cell = Range("A1:A3").Find("Apple", Lookat:=xlWhole)
    Debug.Print cell.Address

End Sub

 
 

Using SearchOrder with Find

The SearchOrder parameter allows use to search by row or by column. In the following sample data we have two occurrences of the text “Elli”.

 
 
VBA Find SearchOrder

 
 
If we search by row we will find the “Elli” in B2 first. This is because we search in the order row 1, then row 2 etc.

If we search by column we will find the “Elli” in A5 first. This is because we search in the order column A, the Column B etc.

 
 
The following code shows an example of using the SearchOrder with this sample data

Sub UseSearchOrder()

    Dim cell As Range

    ' Finds B2
    Set cell = Range("A1:B6").Find("Elli", SearchOrder:=xlRows)
    Debug.Print cell.Address

    ' Finds A5
    Set cell = Range("A1:B6").Find("Elli", SearchOrder:=xlColumns)
    Debug.Print cell.Address

End Sub

 
 

Using SearchDirection with Find

SearchDirection allows you to search forward or backward. So imagine you have the range A1:A7. Searching using xlNext will go in the order

A1, A2, A3, A4, A5, A6, A7

Searching using xlPrevious will go in the order

A7, A6, A5, A4, A3, A2, A1

VBA Find SearchDirection

 
 
Using xlNext with the sample data will return A2 as this where it finds the first match. Using xlPrevious will return A5.

' NOTE: Underscore allows breaking up a line
Sub UseSearchDirection()

    Dim cell As Range

    ' Finds A2
    Set cell = shData.Range("A1:A7") _
        .Find("Elli", SearchDirection:=xlNext)
    Debug.Print cell.Address

    ' Finds A5
    Set cell = shData.Range("A1:A7") _
        .Find("Elli", SearchDirection:=xlPrevious)
    Debug.Print cell.Address

End Sub

 
 

Using xlPrevious with After

It you use the After parameter with xlPrevious then it will start before from the After cell. So if we set the After cell to be A6 then the search order will be

A5,A4,A3,A2,A1,A7,A6.

The following code shows an example of this

Sub UseSearchDirectionAfter()

    Dim cell As Range

    ' Finds A2
    Set cell = shData.Range("A1:A7").Find("Elli" _
            , After:=Range("A6"), SearchDirection:=xlPrevious)
    Debug.Print cell.Address

    ' Finds A6
    Set cell = shData.Range("A1:A7").Find("Elli" _
            , After:=Range("A7"), SearchDirection:=xlPrevious)
    Debug.Print cell.Address

End Sub

 
 

Using MatchCase with Find

The MatchCase parameter is used to determine if the case of the letters matters in the search. It can be set to True or False.

  • True – the case of the letters must match
  • False – the case of the letters does not matter

 
 
The following sample list has two entries for “Elli”. The second has a small letter e

VBA Find MatchCase

 
 
The following code examples shows the result of setting MatchCase to True and False

Sub UseMatchCase()

    Dim cell As Range

    ' Finds A2
    Set cell = Range("A1:B6").Find("elli", MatchCase:=False)
    Debug.Print cell.Address

    ' Finds A6
    Set cell = Range("A1:B6").Find("elli", MatchCase:=True)
    Debug.Print cell.Address

End Sub

 
 

Using MatchByte with Find

The MatchByte parameter is used for languages with a double byte character set. These are languages such as Chinese/Japanese/Korean.

If you are not using them then this parameter is not relevant. They are used as follows

  • True means to match only double-byte characters with double-byte characters.
  • False means to double-byte characters can match with single or double-byte characters.

 
 

Using SearchFormat with Find

Search Format is a bit different than the other parameters. It allows you to search for a cell format such as font type or cell colour.

You need to set the format first by using the Application.FindFormat property. Then you set SearchFormat to True to search for this format.

In the following sample data, we have two cells formatted. Cell A5 is set to Bold and Cell A6 has the fill colour set to red.
VBA Find Search Format

 
 
The following code searches for the bold cell.

Sub UseSearchFormat()

    Dim cell As Range

    Application.FindFormat.Font.Bold = True

    ' Finds A2
    Set cell = Range("A1:B6").Find("Elli", SearchFormat:=False)
    Debug.Print cell.Address

    ' Finds A5
    Set cell = Range("A1:B6").Find("Elli", SearchFormat:=True)
    Debug.Print cell.Address

End Sub

 
 

Using Wild Card

You can search for a cell based on the format only. In other words, the value in the cell is ignored in the search. You do this by placing “*” in the search string.

The following code searches for a cell that is formatted to red. The contents of the cell do not matter.

Sub UseSearchFormatWild()

    Dim cell As Range

    ' Clear previous formats
    Application.FindFormat.Clear
    ' Set format
    Application.FindFormat.Interior.Color = rgbRed

    ' Finds A2
    Set cell = Range("A1:B6").Find("*", SearchFormat:=False)
    Debug.Print cell.Address

    ' Finds A5
    Set cell = Range("A1:B6").Find("*", SearchFormat:=True)
    Debug.Print cell.Address

End Sub

 
 

Important – Clearing Format

When you set the FindFormat attributes they remain in place until you set them again. This is something to watch out for.

For example, imagine you set the format to bold and then use Find. Then you set the format to font size 12 and use Find again. The search will look for cells where the font is bold AND of size 12.

Therefore, it is a good idea to clear the format before you use it. You can clear the format by using the code

Application.FindFormat.Clear

 
 
You can see the we used this in the second SearchFormat example above.

 
 

Multiple Searches

In many cases you will want to search for multiple occurrences of the same value.  To do this we use the Find function first. Then we use the .FindNext function to find the next item.

VBA Find Multiple Searches

 
 
.FindNext searches based on the setting we used in the Find. The following code shows a simple example of finding the first and second occurrences of the text “Elli”.

Sub SearchNext()

    Dim cell As Range
    ' Find first - A2
    Set cell = Range("A1:A9").Find("Elli")
    Debug.Print "Found: " & cell.Address

    ' Find second - A5
    Set cell = Range("A1:A9").FindNext(cell)
    Debug.Print "Found: " & cell.Address

End Sub

 
 
Sometimes you won’t know how many occurrences there is. In this case we use a loop to keep searching until we have found all the items.

We use Find to get the first item. If we find an item we then use a Do Loop with .FindNext to find the rest of the occurrences.

FindNext will wrap around. That is, after it finds A9 it will continue the search at A1. Therefore, we store the address of the first cell we find. When FindNext returns this cell again we know we have found all the items.

The following code will find all the occurrences of Elli

Sub MultipleSearch()

    ' Get name to search
    Dim name As String: name = "Elli"

    ' Get search range
    Dim rgSearch As Range
    Set rgSearch = Range("A1:A9")

    Dim cell As Range
    Set cell = rgSearch.Find(name)

    ' If not found then exit
    If cell Is Nothing Then
        Debug.Print "Not found"
        Exit Sub
    End If

    ' Store first cell address
    Dim firstCellAddress As String
    firstCellAddress = cell.Address

    ' Find all cells containing Elli
    Do
        Debug.Print "Found: " & cell.Address
        Set cell = rgSearch.FindNext(cell)
    Loop While firstCellAddress <> cell.Address

End Sub

 
 
The output from this code is
Found: $A$2
Found: $A$5
Found: $A$8

 
 

Finding the Last Cell Containing Data

A very common task in VBA is finding the last cell that contains data in a row or colum. This does not use the VBA Find function. Instead, we use the following code to find the last row with data

' Find the last row with data in column A
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

' Find the last row with data in column C
LastRow = Cells(Rows.Count, 3).End(xlUp).Row

 
 
To find the last column with data we use similar code

' Find the last column with data in row 1
lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

' Find the last column with data in row 3
lLastCol = Cells(3, Columns.Count).End(xlToLeft).Column

 
 

Finding Cells with Patterns

If you want to find cells with certain patterns then you have to use the Like operator rather than Find.

For example, to find  the all the names starting with E you could use the following code

' Print all names starting with the letter E
Sub PatternMatch()

    Dim cell As Range
    ' Go through each cell in range
    For Each cell In Range("A1:A20")
        ' Check the pattern
        If cell Like "[E]*" Then
            Debug.Print cell
        End If
    Next

End Sub

 
 
If you want to know more about this then check out Comparing Strings using Pattern Matching.

To see a real-world example of using pattern matching check out Example 3: Check if a filename is valid.

 
 

An Alternative to using VBA Find

If you are expecting a large number of hits then using an array is a better option. You can read a range of cells to an array very quickly and efficiently.

The following code reads the cell values to an array and then reads through the array to count the items.

Sub UseArrayToCount()

    Dim arr() As Variant
    ' read cell range to array
    arr = Sheet2.Range("A1:B25")

    Dim name As Variant, cnt As Long
    ' Go through the array
    For Each name In arr
        ' Count in the name 'Ray' is found
        If name = "Ray" Then
            cnt = cnt + 1
        End If
    Next name

    Debug.Print "The number of occurrences was: " & cnt

End Sub

 
 
If you want to find out more about arrays then check out the post The Complete Guide to Using Arrays in Excel VBA.

 
 

Find and Replace

To  do a find and Replace you can use the Replace function. It is very similar to using the Find function.

The replace function is outside the scope of this post although a lot of what you read here can be used with it. You can see the details of it at Microsoft – VBA Replace Function

What’s Next?

If you want to read about more VBA topics you can view a complete list of my posts here. I also have a free eBook(see below) which you will find useful if you are new to VBA.

 
 

Get the Free eBook

How To Ace the 21 Most Common Questions in VBA

Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the blog, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.

Free VBA eBook

 
 


 
 

48 comments

      1. Sir, your subject knowledge is brilliant and easy to understand.
        Please suggest how to become great in VBA like you. Want to know some open source VBA project sites.
        Thanks a lot. Learnt a lot from this website.

        1. Hi Abhishek,

          You can try SourceForge.Org for open source.
          The way to become great is by practising and creating real world applications. It’s important to evaluate each project, learn from mistakes and see how you could have done it better.

  1. Hello Paul,

    congratulations for both the website and the blog. They are amazing and your explanations are very clear and helpful.
    In my opinion, one of the best sites for learning VBA.

    Thanks a lot.

  2. Huhuhuhu…. Thank you… thank you… for more than 3 days looking for a good solution in searching… it’s is difficult to being self study without teacher…

    Once again sir.. Thank you very and God bless….

  3. What I would like is an explanation on how VBA could show the built-in Excel find dialog box (hint: Application.Dialogs(xlDialogFormulaFind).Show) and then how a VBA routine could read what the user entered for criteria into that dialog after it finishes. But maybe this is impossible.

    1. It could be done but requires using Windows API. The following code will get you started

      ' Declare Window API function
      Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
      (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
      
      Sub GetText()
          
          ' Show and wait for user to close it
          Application.Dialogs(xlDialogFormulaFind).Show
          
          ' After it is hidden show again to get text
          Application.CommandBars.FindControl(ID:=1849).Execute
          
          ' Get a handle to the window
          Dim hMainWind As Long
          hMainWind = FindWindow(vbNullString, "Find And Replace")
      
          ' Now you need to
          ' 1. get a handle to the textbox
          ' 2. read the text
          ' 3. hide the Find Window
      
      End Sub
      
  4. Hi Paul,

    Firtsly, great blog!
    I’ve been visiting for a while now and learned a lot of useful things from your posts – thank you so much!

    I have a quick question today – Is there a way to set the Application.FindFormat property using the inequality operator “”?

    16777215 is the color.index for “no fill” and in my search for a string I want only to find cells that do have fill colour. But unfortunately I’m getting an error when i type the following:

    Application.FindFormat.Interior.Color 16777215

    Any idea how to get around this?

    Thanks,
    J.

    1. Hi J,

      When the equals is used with FindFormat it means “assigned” rather than “equals”(see second table here for different examples in VBA).

      So you can only search for a given color.

      Paul

  5. Hi Paul

    I have a big problem. I started coding in VBA a week ago, as a project for my vacation work. I have a UserForm and a table. The user tipes in a SampleID in the userform and some data. He clicks “Add to table”. The code should do:

    search for the Sample ID and add all other data in that row. My code looks like:

    ID = SampleID.Value
    Set Obj = Sheets(“Tests Results”).ListObjects(“Test_results”).Find(ID)
    Set objNewRow = Obj.ListRows(AlwaysInsert:=True)
    y = objNewRow.Index

    With Obj
    .ListColumns(“Test Lab”).DataBodyRange(x) = Me.ComboBox_LAB.Value
    .ListColumns(“Flammability Type “).DataBodyRange(x) = Me.ComboBoxFlamm.Value
    .ListColumns(“Avg-Smoke Density Pass Value (Ds)”).DataBodyRange(x) = Me.ComboBoxSDpass.Value
    .ListColumns(“Maximum Smoke Density Pass Value_Flaming_ (DS)”).DataBodyRange(x) = Me.ComboBoxMAXSD.Value
    .ListColumns(“Smoke Density Mode”).DataBodyRange(x) = Me.ComboBoxSmoke.Value

    End With
    End Sub

    I get the error 438 “Object does not support this property or method” in line 2

    I have spent hours trying to figure this out and still dont get any results.
    Could you please help me?

    1. Hi Pascal,

      There is no Find method for the Table. You need to get the Range of the table e.g.
      Set Obj = Sheets(“Tests Results”).ListObjects(“Test_results”).Range.Find(ID)

  6. Hi Paul,

    I’m stuck (note: newbie @ VBA!), and hoping you can point out my error.

    Code snippet:

    Dim rowDescription As Integer
    Dim rowPartnumber As Integer
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim FoundCell As Range

    Set wb = ActiveWorkbook
    Set ws = ActiveSheet

    Const WHAT_TO_FIND1 As String = “Description”
    Const WHAT_TO_FIND2 As String = “Part Number”

    Set FoundCell = Cells(1, 1)
    rowPartnumber = 1

    ‘find the next “Description”. This is TOP of Comment
    FoundCell = ws.Range(“A:A”).Find(What:=WHAT_TO_FIND1, After:=Cells(rowPartnumber, 1), LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

    /Code snippet:

    My data has the string “Description” in cell A10.

    However, the result of this code is that the string “Description” is populated into cell A1!

    For the life of me, I cannot figure out what I am doing wrong.

    Can you help me?

    1. Hi,

      The line Set FoundCell = Cells(1, 1) sets the range of FoundCell to be A1.

      The line FoundCell = ws.Range(“A:A”).Find( places the result of Find in FoundCell which is A1.

      It should be Set FoundCell = ws.Range(“A:A”).Find(

      Regards
      Paul

  7. Hi Paul,

    Thanks for the great blog!

    I’m not entirely sure if the solution above will apply to the issue that I currently have. I will play around with it and see how it is different.

    My scenario is as follows:

    (Sheet1)
    Contains mass data with different headers for different types of financial information. The headers change from month to month and if i dont have information for that specific month. The entire column will not display in the report.

    (Sheet2)
    I need code that will produce a summary report based on Sheet1 that will only display certain identified columns. I have written code to find the desired header in sheet1, selecting the entire column, and the copying it to my sheet 2 report.

    (My issue:)
    As soon as my code cant find the column header, it produces an error and halts the entire process that is supposed to follow. I have about 60 different column headers i need to find in my sheet1

    Please help if you can and thank you in advance!

    Regards,

    Maartin

  8. Paul, In the above examples the search is done in one column. I am needing the Find for row of dates Ex.(“B3:B10”) . The Dates are the column headings for each week on a sales report. Needing to Find the column that matches the Date. The search always comes up with nothing. Thank you for help

  9. Hi Paul, I have a dynamic form and my project is password protected. but when I run the program as the project is password protect it says VBA password protect. How do I pass the password to the dynamic form so that it processes. Please help. I liked your find tutorial and examples.

  10. Thanks Paul!!

    This whole site is extremely useful – I’m so glad I found you!

    How would you go about finding the last used cell in a worksheet if you don’t know what row/column the farthest non-empty cell is?

    Thanks again.

    1. You can use the UsedRange property to get the used cells.

      Sub GetLastCell()
      
          Dim rg As Range
          Set rg = Sheet1.UsedRange
          
          Debug.Print "Last Row is : " & rg.Rows(rg.Rows.Count).Row
          Debug.Print "Last Column is :" & rg.Columns(rg.Columns.Count).Column
      
      End Sub
      
  11. Considering this line of code:
    Range(“A1:A5”).Find “John”
    Suppose the range A1:A5 is to be picked by the user each time the macro/ line is run, how to do it???

    N.B.: I believe it is almost impossible.

    Please HELP.

    Thank You Very Much.

    1. Sub FindName()
      
          ' Get range
          Dim rg As Range
          Set rg = Application.InputBox("Please select a range", "Select Range", Type:=8)
          
          ' Find John
          Dim rgFind As Range
          Set rgFind = rg.Find("John")
          
          ' Display Result
          If rgFind Is Nothing Then
              MsgBox "could not find John"
          Else
              MsgBox "Found John at cell" & rgFind.Address
          End If
          
      End Sub
      
  12. Paul – I’m just getting started with VBA, and I have the following in my script:

    Dim FirstOne As Range
    Dim sht1 As Worksheet
    Set FirstOne = sht1.Cells.Find(What:=”Type”, After:=Range(“A1”), LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=True)

    What I have found is that if the word Type is in cell A1, it ignores it and finds the next occurrence. I tried it without the After clause, and it still ignored the contents of A1. My solution was to add code that said “If you find the word Type in cell A1, then insert a row and then do the Find.” That seems to have solved the problem, but I was wondering if there was something I’m doing wrong.

    And BTW, I have found your site to be extremely informative! Keep up the good work!

    Thanks!

    1. Hi Jim,

      If you change the first part from sht1.Cells to sht1.Range(“A1:A10”) it will work as long as the cell is in the range.

      If you want to use the entire sheet as the range then do this

      Dim rg As Range
      Set rg = sht1.Range(sht1.Cells(1, 1), sht1.Cells(sht1.Rows.Count, sht1.Columns.Count))
      
      Set FirstOne = rg.Find(What:="Type", After:=Sheet1.Range("A1"), LookIn:=xlValues, _
          LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True)
      
  13. Wow Paul! Very thorough, great read and easy to understand.

    I’m using find in a named range, but somehow, it first finds the 2nd instance, and then with a FindNext it wraps around (taking this term from your article…) and finds the first instance.

    My code:
    Dim poRange As Range, firstFound As String
    Set poRange = Sheets(“Log”).Range(“PO”).Find(Sheets(“Tracking”).Range(“C13”).Value)
    firstFound = poRange.Address

    When I run my code, the first found will always be the 2nd instance, can’t get it through to find first the first instance.

    I’m simply stuck here. Any help with this?

    1. Thanks Harry, Glad you like it.

      Without seeing the data and your find settings it is difficult to say.

      Try different settings and see how it affects the result.

      1. Thanks Paul,

        What do you mean with ‘find settings’?
        Okay, I think I got it…
        “PO” is a named range for the first column in a table (in my case it is referencing range “A2:A25”).
        What I found is, that Find simply starts from the 2nd row in the range, and then wraps around, so if the first occurrence of my find is let’s say in A6, then it’ll work fine and return A6 as the firstFound, but if my find is for something that has its first instance in A2, then it’ll first find the 2nd instance and all the rest occurrences, and only then it’ll wrap around and get back to A2.

        Any reason why this is so (sorry but no way here to attach my actual data for a better explanation)?

        1. Hi Harry,

          The behaviour of Find is that it starts searching after the first cell in the range.
          I’m not sure why this is that case. You can use the After parameter to get around this.

          Dim rg As Range
          Set rg = Sheet1.Range("A2:A25").Find("a", After:=Sheet1.Range("A25"))
          

          Paul

          1. Thanks much Paul!
            Interesting why, but anyhow, in my case of named range, I’ll have to somehow figure the last cell of the range to place it in the ‘After’ parameter.

  14. Is it possible to search for a value for e.g. “8” and replace it with “9” and highlight the cell’s background colour or text colour.

    Much appreciated.

    1. Dim c As Range
      Set c = Range("A1:A5").Find(9)
      
      If Not c Is Nothing Then
          c.Value = 8
          c.Interior.Color = rgbBlue
          c.Font.Color = rgbRed
      End If
      
  15. When I perform a search using find command on a range in sheet1 and then move to sheet2 to perform some action with the found value and then return to sheet1 and then call .FindNext I am only getting the first match. What am I doing wrong? Thanks for any help.

  16. Pshew, almost two years since this blog was posted and you are still getting questions. Thank you for all of your time and guidance.
    I may have a similar question to ‘Bob the Macro Builder’ with regards to an error with the .FindNext function. I have two .Find functions with a .FindNext at the end. I would like to continue the first .Find but as expected, the .findNext references the most recent .Find, leaving me with an incorrect search. Is there a way to distinguish between two .Find functions or terminate one of them to separate the two?

    1. Find is like using the Find dialog in Excel. FindNext will use the conditions of the previous find.

      How you would solve you problem really depends on your code and what you are trying to do.

  17. Hi, I have created the below code to search a value and highlight it in a specific sheet. Just want the search to be non-case sensitive and not search the complete word but the “like” e.g. if I am searching “asd”, I should be able to find all “RasdFE”, “ASD” and “asd”.

    Sub findValue()

    Dim cell As Range

    Dim xlRange As Range
    Dim xlCell As Range
    Dim xlSheet As Worksheet

    Dim Valuerange As Range
    Dim valuecell As Range
    Dim valueSheet As Worksheet

    Set valueSheet = ActiveWorkbook.Worksheets(“Values”)
    Set Valuerange = valueSheet.Range(“A2:A1000”)

    Set xlSheet = ActiveWorkbook.Worksheets(“Data”)
    Set xlRange = xlSheet.Range(“A1:N300”)

    For Each valuecell In Valuerange
    If Not IsEmpty(valuecell.Value) Then
    For Each xlCell In xlRange
    If xlCell.Value = valuecell Then
    xlCell.Interior.Color = RGB(255, 255, 0)
    End If
    Next xlCell
    End If
    Next valuecell

    End Sub

    1. Hi Amit.

      You can use the Like keyword to check for patterns

      Dim sPattern As String
      sPattern = "*[Aa][Ss][Dd]*"
      
      Debug.Print "ASD" Like sPattern
      Debug.Print "asd" Like sPattern
      Debug.Print "RasdFE" Like sPattern
         
      

Leave a Reply

Your email address will not be published. Required fields are marked *