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



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


Download the Source Code


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.

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.

If you want to try these examples you can download the workbook from the top of this post.

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."
    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 example 3a

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

You can try these example for yourself by downloading the workbook from the top of the post.

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: value, formula, threaded comment and note.

To see a working example of this code you can download the source code from the top of this post.

' Searches in value, formula, threaded comment and note.
' https://excelmacromastery.com/excel-vba-find/
Sub UseLookIn()

    ' Finds A2
    Dim rgFound As Range
    Set rgFound = shLookin.Range("A1:A5").Find("Apple", LookIn:=xlValues)
    Debug.Print "Found 'Apple' as value in: " & rgFound.Address

    ' Finds A3
    Set rgFound = shLookin.Range("A1:A5").Find("Apple", LookIn:=xlFormulas)
    Debug.Print "Found 'Apple' as formula in: " & rgFound.Address

    ' Finds A4
    Set rgFound = shLookin.Range("A1:A5").Find("Apple", LookIn:=xlCommentsThreaded)
    Debug.Print "Found 'Apple' as comment threaded in: " & rgFound.Address
    ' Finds A5
    Set rgFound = shLookin.Range("A1:A5").Find("Apple", LookIn:=xlNotes)
    Debug.Print "Found 'Apple' as note in: " & rgFound.Address

End Sub

Important note that I have used xlCommentsThreaded for the third one as threaded comments are used in Office 365. If you are using an older version that doesn’t have threaded comments then use xlComments.

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.

' https://excelmacromastery.com/
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

You can try these example for yourself by downloading the workbook from the top of the post.

Using SearchOrder with Find

The SearchOrder parameter allows us 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

' https://excelmacromastery.com/
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 A6.

' NOTE: Underscore allows breaking up a line
' https://excelmacromastery.com/
Sub UseSearchDirection()

    Dim cell As Range

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

    ' Finds A6
    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


The following code shows an example of this

' https://excelmacromastery.com/
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 show the result of setting MatchCase to True and False

' https://excelmacromastery.com/
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 the WildCard

We can use the asterisk symbol(*) as a wild card when searching for text. The asterisk represents one or more characters.
For example
“T*” will find any word that starts with T.
“To*” will find any word that starts with To.
“*y” will find any word that ends with y.
“*ey” will find any word that ends with ey.

The code below shows examples of using the wildcard based on this data:
vba find wild card

' Examples of using the wild card
' https://excelmacromastery.com/excel-vba-find/
Sub WildCard()

    Dim rgFound As Range
    ' Finds Tom in A2
    Set rgFound = shWildCard.Range("A1:A6").Find("T*")
    Debug.Print rgFound.Value & " was found in cell " & rgFound.Address

    ' Finds Tim in A5
    Set rgFound = shWildCard.Range("A1:A6").Find("Ti*")
    Debug.Print rgFound.Value & " was found in cell " & rgFound.Address
    ' Finds Tommy in A4
    Set rgFound = shWildCard.Range("A1:A6").Find("*my")
    Debug.Print rgFound.Value & " was found in cell " & rgFound.Address
    ' Finds Ellen in A3
    Set rgFound = shWildCard.Range("A1:A6").Find("*len*")
    Debug.Print rgFound.Value & " was found in cell " & rgFound.Address
    ' Finds Helen in A6
    Set rgFound = shWildCard.Range("A1:A6").Find("*elen*")
    Debug.Print rgFound.Value & " was found in cell " & rgFound.Address
End Sub


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 color.

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:

' Find the cell which has a bold format
' https://excelmacromastery.com/excel-vba-find/
Sub UseSearchFormat()

    Dim findText As String
    findText = "Elli"

    ' Clear previous formats and set new format
    Application.FindFormat.Font.Bold = True

    ' Finds A2
    Dim rgFound As Range
    Set rgFound = Range("A1:A6").Find(findText, SearchFormat:=False)
    Debug.Print "Found '" & findText & "' in cell: " & rgFound.Address

    ' Finds A5
    Set rgFound = Range("A1:A6").Find(findText, SearchFormat:=True)
    Debug.Print "Found '" & findText & "' in cell: " & rgFound.Address

End Sub


Using Wild Card with Format

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 – the cell color in this example is set to red. The contents of the cell do not matter:

' Find the cell which is formatted - contents do not matter
' https://excelmacromastery.com/excel-vba-find/
Sub UseSearchFormatWild()
    ' Clear previous formats and set new format
    Application.FindFormat.Interior.Color = rgbRed

    ' Finds A2 as it ignores the format and finds the first cell with any contents
    Dim rgFound As Range
    Set rgFound = shSearchFormat.Range("A1:B6").Find("*", SearchFormat:=False)
    Debug.Print "Found format in cell: " & rgFound.Address

    ' Finds A5 as this is first cell with the format set to interior color as red
    Set rgFound = shSearchFormat.Range("A1:B6").Find("*", SearchFormat:=True)
    Debug.Print "Found format in cell: " & rgFound.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 as I have done in the above examples.


You can see 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”.

' https://excelmacromastery.com/
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

' https://excelmacromastery.com/
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
        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
' https://excelmacromastery.com/
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

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.

' https://excelmacromastery.com/
Sub UseArrayToCount()

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

    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?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars and all the tutorials.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)



  1. Great lesson! Thanks for taking the time to help us new VBA coders. May I suggest a troubleshooting section? For example, I’m trying to use find to locate each occurrence of the phrase “last test” in a column, and then delete the row it is found in. Just like you said, however, I get an error (run time 91) if this phrase is not found. So, I use the “if not….Nothing…technique, and Excel won’t let me do it ! Excel objects to my use of the word “Nothing”, ( or maybe Excel just doesn’t like me :/. Thanks again!

    1. I am trying to find all entries in a range which are in bold font. Could you please send me the code for that? Many thanks.

  2. What is the line code to search for natural page break? I would like to find all page break and insert a copied row after the page break?

  3. Hello everyone ! I am not experienced in vba coding and I have a seemingly simple question. I would like to create a click button macro which will find and select every currency formated cell which includes the euro symbol € or every currency formated cell than has a sum above 0. At the moment this code :
    Sub FINDER()

    Cells.Find(What:=”€”, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

    End Sub

    finds the € symbol as i want but not on currency formated cells which by default-automatically add the euro symbol to the sum..Please kindly help =)

  4. Really good page, but can you please update it with the default values for each parameter? I’ve been trying to find out but struggling. Many thanks.

  5. Hi Paul,
    as you have mentioned that while finding just the cell format.
    we can use “*”, this will ignore the values .
    However i have tested the results and at times when the cell is blank and we need to search for the cell format ,say rgbred. then the following code will not give the desired results.

    Application.FindFormat.Interior.Color = rgbRed
    Set cell = Range(“A1:B6”).Find(“*”, SearchFormat:=True)
    Debug.Print cell.Address

    For correct usage we will have to uses “” instead of “*”

    so the correct code would be

    Application.FindFormat.Interior.Color = rgbRed
    Set cell = Range(“A1:B6”).Find(“”, SearchFormat:=True)
    Debug.Print cell.Address

    Hope you will give it a thought.

    1. I discovered something similar. You have to type “” rather than “*” in order to find empty formatted cells.

  6. Initially I was looking to see if you could do something along the lines of .FIND(What:=”0″….) but that doesn’t appear to be an option. In your last example you load the range into an array as that’s faster than checking the cell each time. I was wondering why “Dim arr() As Variant: arr = Sheet2.Range(“A1:B25″)” works and “Dim arr() As Variant: arr = Worksheets(“Sheet2”).Range(“A1:B25″)” returns a Type Mismatch. The only difference I can see is that the first way is an “Object/Range”, while the second is a “Variant/Object/Range” in the watch window.

    1. That’s an interesting one.

      I’m not sure why it’s a different type based on using the worksheet name. However, if you add value to the range it will work fine.

      Dim arr As Variant
      ' read cell range to array
      arr = Worksheets("Sheet2").Range("A1:B25").Value
  7. In sheet 1 there are items from range (A1:o30) I found the particular value how to copy them immediately after find?

  8. Sub test()

    Dim name As String: name = “vijay”

    Dim rgsearch As Range
    Set rgsearch = Range(“a1:a9”)

    Dim cell As Object
    Set cell = rgsearch.Find(name)

    Dim firstcessaddress
    firstcessaddress = cell.Address
    Range(firstcessaddress).Copy Range(“f2”)

    Debug.Print “Found: “; cell.Address
    Set cell = rgsearch.FindNext(cell)
    Range(cell.Address).Copy Range(“f1”).End(xlDown).Offset(1, 0)
    Loop While firstcessaddress cell.Address

    End Sub

    With the above i expect it to copy only two Vijay instead it copied 3 times i dont no why? What should be alternate code?

  9. It has been a long time since I last did anything with macros. I had already forgotten about them. Your page is an excellent source of information, clear, logical and precise.
    Thank you so much for returning my self-respect as an engineer!!!

  10. How can I do a search for the value only in column “a” and not in the rest of the columns
    I’m looking for a value, and retrieving the rest of the columns
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets(“Sheets1”)
    Dim Found As Range
    Set Found = ws.Range(“A1:C3”).Find(What:=ComboBox1.Value, LookAt:=xlWhole)
    If Not Found Is Nothing Then
    Me.TextBox1 = Found.Offset(, 1)
    Me.TextBox2 = Found.Offset(, 2)
    Me.TextBox1 = “”
    Me.TextBox2 = “”
    End If

    1. Search on the column e.g. ws.Range(“A1:A3”).

      Then expand the range when you get the result using Resize:

      Set Found = Found.Resize(1,3)

      where 3 is the number of columns to include in your retrieval.


  11. Hi Paul, thank you for an excellent source of information.
    Please can you assist with how to use the .find function to find the next non-empty cell.

  12. Hi Paul,
    I have been struggling for a while to find the right searchway for my purposes.
    I am using a userform that contains a textfield and a cmdSearch plus an Excelsheet with a larger list of names and IDnumbers. My wish is to enable the user to enter the ID in the txtID of the userform and by clicking on cmdSearch find the location in the list.
    Is there a way to make the textID an accepted variant?

    1. Hi Sandra,

      I’m not 100% clear on your question. I understand what you say up to that point. What do you mean by “an accepted variant”?

      You can read a value from the textbox using txtID.value. You can use this as the What parameter in Find.


  13. Hi, I have created an archive button where it copies an entry to another sheet and then deletes the entry from the original list.
    I also need it to find all the times that activecell value appears in a range of AB to AG, delete those entries and move the cells below it up in order to remove the blank.
    Is there a way I can enter some find code into the code I already have in order to achieve this?

  14. the best find VBA tutorial i have seen. noob here!
    still wondering how to search multiple words in a list e.g. A,B,C,D,E in the range.

  15. Hi,

    Great guide!

    Is there a way to find a value entered in a cell. In my case the user enters a 4 digit code in cell A4, I want to do a Find”[Whats entered in cell A4]”

    1. You can reference the cell directly using Range(“A4”).Value2.
      If you need to check when the user enters the value you can use the worksheet change event.

  16. Hi Paul,
    You mentioned that character * is used as a wild card for range.find method. Then, how to locate cells that contain character * ? Or, say to turn of * as a wild card?

  17. I am learning the coding part, and I am stuck at the scenario like : I will be selecting the entire column based on the column heading and then I need to perform find operation only in that selected column.

    For j = 1 To UBound(Fstrsll)
    Debug.Print Fstrsll(j)
    Debug.Print colsllRange

    Set sllRange = Selection
    Debug.Print sllRange.Address

    Set FoundCell = Range(sllRange.Address).Find(What:=Fstrsll(j), After:=LastCell) ****** Type miss match error
    If Not FoundCell Is Nothing Then
    FirstAddr = FoundCell.Address
    End If
    Do Until FoundCell Is Nothing
    Debug.Print FoundCell.Address
    c = c + 1
    ReDim Preserve addr(0 To c)
    ReDim Preserve sllmatch_addr(0 To c)
    addr(c) = FoundCell.Address
    sllmatch_addr(c) = FoundCell.Address

    Set FoundCell = Range(colsllRange).FindNext(After:=FoundCell)
    If FoundCell.Address = FirstAddr Then
    Exit Do
    End If

    Can you help me with this logic or any error I did.


  18. Hello,
    How can I get “find” to find something in cell A1, when the activecell is A1? I tried but “find” always finds the next occurrence and only after all occurrences were found, it funds the one in A1.
    Thank you!

  19. For some reason that I don’t understand, the CLEAR ALL command does not always work. After selecting the entire sheet and then clicking CLEAR ALL, I type Ctrl+End and the cursor jumps down to a cell far down the sheet. The sheet shows no contents in any cell. I thought that CLEAR ALL was to clear out contents, formatting, . . . everything out of selected cells. Why is it not working?

  20. I apologize for the bad wording, but I do not know English and do automatic translation from the Internet.

    I’m asking for a Macro to find a text value in a cell in an Excel sheet.

    But the macro must receive the value directly from the selected cell and look for it in another sheet.

    This cell value will be different each time. That’s why it won’t be known from the beginning.

    How do I write the macro to find the value directly from the cell, without knowing it beforehand?


  21. How do I get a value below 50, for example using the code below?

    I couldn’t adapt [what: = “0”} to do what I need:

    – – –

    Sub FindLow()

    With Range(“B1:B16″)
    Set Rng = .Find(what:=”0”, lookat:=xlWhole, LookIn:=xlValues)
    If Not Rng Is Nothing Then
    firstAddress = Rng.Address
    Rng.Offset(, 1).Value = “low”
    Set Rng = .FindNext(Rng)
    Loop While Not Rng Is Nothing And Rng.Address firstAddress
    End If
    End With

    End Sub

  22. It looks like .Find when looking for, say 1E-20, inherently uses the .Value value of what is in the searched cells regardless of using LookIn:=xlValues or =xlFormulas. That is, for example, if the cell in which 1E-20 occurs is formulated as currency (a useful, real situation) it will not be found, as .Find thinks its value is 0. The way around it is your “An Alternative to using VBA Find”, but using .Value2 in the 4th line instead of .Value .

  23. Thanks for your contribution!
    I have the next problem. I want to find in a column a value that is inside of a cell, this value changes. So, if I have a range of (B1:B58) and I want to find a value that is inside of the cell (D3) how can I select the cell that contains this value in my range (B1:B58)?


  24. Hi can you please help. I am creating a validation list of strings . I am trying to set up a VBA find function that will search a spreadsheet for the string selected in the validation list
    Sub Find_A_String()
    Cells.Find(What:=”The string selected from the validation list”, After:= _
    ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns _
    , SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False). _
    End sub
    In short how do I define “The string selected from the validation list”

  25. This is the best site for VBA and Macros…
    Lately I am also following your YOUTUBE channel also…

    Awesome work… keep going..

  26. Hello,
    I am just getting mismatch error and can’t find source of that.
    Mismatch 13 error.
    With or without argument “After” it doesn’t work.
    Really have no idea where is problem.
    ObjID is range
    other variables are Long.
    shtClean is worksheet
    With shtClean.Range(Cells(2, ObjIDCol), Cells(LRowClean, ObjIDCol))
    Set ObjID = .Find(DataOut.ObjectID, After:=shtClean.Cells(2, ObjIDCol), LookAt:=xlWhole, _
    SearchFormat:=xlValues, SearchOrder:=xlByRows)
    End With

    Thank you in advance.

    1. Hey,
      somehow i found solution.
      I don’t understand why, but it works.

      First i selected area i use .Find
      and then i removed every argument in . Find and left only object to find.

      ‘my selected area
      shtClean.Range(Cells(2, ObjIDCol), Cells(LRowClean, ObjIDCol)).Select

      ‘my find
      With shtClean.Range(Cells(2, ObjIDCol), Cells(LRowClean, ObjIDCol))
      Set ObjID = .Find(DataOut.ObjectID)
      End With

      Dont know really why method i mention above doesn’t work…

      Anyhow trying with different configuration did the job.


  27. Paul,
    I need to find the Cell Address of the found data or move to the bottom of the list if the data is not found.
    Trying Found = Range(“B:B”).Find(UID).Activate then it works as long as the data is found, but I immediately get the Run-time error 91 when the data is not found. If I remove the .Activate then I need another way to find the cell address.

    Here is the code I’m working on:
    ‘Populating The Report
    Do While Not ActiveCell.Value = 0 ‘Continue until there is no data left on the Data sheet

    ‘Load Variables for this row
    UID = Range(“H” & ActiveCell.Row).Value
    Name = Range(“A” & ActiveCell.Row).Value
    EMail = Range(“D” & ActiveCell.Row).Value
    Position = Range(“G” & ActiveCell.Row).Value
    Department = Range(“E” & ActiveCell.Row).Value
    Location = Range(“F” & ActiveCell.Row).Value
    TTitle = Range(“B” & ActiveCell.Row).Value
    CompDate = Range(“K” & ActiveCell.Row).Value
    MName = Range(“I” & ActiveCell.Row).Value

    ‘Populate Manager Name on Report
    Found = Range(“B:B”).Find(UID).Activate
    If Found = False Then
    Range(“B” & Rows.Count).End(xlDown).Offset(1).Select
    Range(NameC & ActiveCell.Row).Value = Name
    Range(UIDC & ActiveCell.Row).Value = UID
    Range(PositionC & ActiveCell.Row).Value = Position
    Range(EMailC & ActiveCell.Row).Value = EMail
    Range(DepartmentC & ActiveCell.Row).Value = Department
    Range(LocationC & ActiveCell.Row).Value = Location
    Range(MNameC & ActiveCell.Row).Value = MName
    MRow = ActiveCell.Row
    TFound = Range(“1:1”).Find(TTitle).Activate
    If ActiveCell.Offset(MRow – 1, 0).Value = CompDate Then
    End If
    End If
    MRow = ActiveCell.Row
    TFound = Range(“1:1”).Find(TTitle).Activate
    If ActiveCell.Offset(MRow – 1, 0).Value = CompDate Then
    End If
    End If
    End If


Leave a Reply

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