“I know well what I am fleeing from but not what I am in search of” – Michel de Montaigne
- 1 Introduction
- 2 Download the Source Code
- 3 What is the VBA Find Function?
- 4 Introduction
- 5 VBA Find Parameters
- 6 The Find Return Value
- 7 How to do a Simple Find
- 8 Using After with Find
- 9 Using LookIn with Find
- 10 Using LookAt with Find
- 11 Using SearchOrder with Find
- 12 Using SearchDirection with Find
- 13 Using MatchCase with Find
- 14 Using MatchByte with Find
- 15 Using the WildCard
- 16 Using SearchFormat with Find
- 17 Multiple Searches
- 18 Finding the Last Cell Containing Data
- 19 Finding Cells with Patterns
- 20 An Alternative to using VBA Find
- 21 Find and Replace
- 22 What’s Next?
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:
- The Find function is a member of Range.
- It searches a range of cells containing a given value or format.
- 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)
When you do this the following dialog will appear:
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.
|What||Required||The value you are searching for||Any VBA data type e.g String, Long|
|After||Optional||A single cell range that you start your search from||Range("A5")|
|LookIn||Optional||What to search in e.g. Formulas, Values or Comments||xlValues, xlFormulas, xlComments|
|LookAt||Optional||Look at a part or the whole of the cell||xlWhole, xlPart|
|SearchOrder||Optional||The order to search||xlByRows or xlByColumns.|
|SearchDirection||Optional||The direction to search||xlNext, xlPrevious|
|MatchCase||Optional||If search is case sensitive||True or False|
|MatchByte||Optional||Used for double byte languages||True or False|
|SearchFormat||Optional||Allow searching by format. The format is set using Application.FindFormat||True 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
- The Range to search
- The value you are searching for
- The Range to store the returned cell
Let’s take the following sample data
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
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. When, the Excel Find Dialog is used, the active cell is considered the After cell. In other words, this cell is the starting point for the search. In VBA, if no After parameter is specified then the search starts at the top-left cell of the range.
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.
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
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"))
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 Contains||Result||LookIn value is|
|Apple||Apple||Value and Formula|
|="App" & "le"'||Apple||Value 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
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.
- xlWhole means the search value must match the entire cell contents.
- 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.
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”.
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
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
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.
“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:
' 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.
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.Clear 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 Application.FindFormat.Clear 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.Clear 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 Application.FindFormat.Clear 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.
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.
.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 Do Debug.Print "Found: " & cell.Address Set cell = rgSearch.FindNext(cell) Loop While firstCellAddress <> cell.Address End Sub
The output from this code is
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 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.
' 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
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.)
Your knowledge is really helped me a lot, and you are always the first go to resource for me.
fnBonus = Range(“tbBonus[Empcode]”).Find(empcd).Offset(0, 1).Value
The above code is not taking the first record from the following table
so, fnBonus(1) gives result as 5900
I thought the following would solve the problem, but it did not and results into an #VALUE! error
fnBonus = Range(“tbBonus[Empcode]”).Find(empcd, After:=Range(“tbBonus[[#Headers],[Empcode]]”)).Offset(0, 1).Value
Any idea what I may be missing here.
Love your work, keep it up.
it would be nice to discuss tables. Find for tables does not return a range but rather the cells value
I want to call the Find and Replace dialog when an Excel workbook opens. I see application.dialogs but what index do I use? Excel reports a count of 1430 dialogs but when I scroll through the list, I can’t see one that looks or works like Find and Replace.
I found the answer to my question here: https://www.mrexcel.com/board/threads/vba-for-find-dialog-box.71530/
Try formatting a cell with #,##0 and enter inside of it formula =200+800
Then try to find 1000 *by value* and let me know if you find that cell.
What do you think?
Thank you for your time & best regards,
PS: identical behavior for VBA’s FIND method
If you are using VBA, why not evaluate the cell value. If the cell value = 1000 do X else do Y ?
If data in A1:A10 and I try to find something which is in A1, then it gives the answer as 2.
For example A1:A10 contains below and if I try to find “a”, it gives an answer of 2 rather than 1
Ws.Cells(4, “E”) = Ws.Columns(“A”).Find(what:=”a”, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlRows, searchdirection:=xlPrevious, MatchCase:=False).Row
In this case you’re searching backward and start in cell A10, then A9, A8 … A3, A2, A1. The first match found for “a” is then in cell A2. Your problem will be solved when you change “searchdirection:=xlPrevious” to “searchdirection:=xlNext”. Then it will start searching from the first cell in your range.
I have a similar problem and am an experienced VBA programmer. I have a bank statement export with no headers and dates descending. Like 11/3/21 for 15 rows, then repeating 11/2/21, etc. LastDateLoaded is date field with “11/3/2021”. a VBA find:
Find seems to be acting like there is an assumed header. I’m going bonkers.
WSwfb.Range(“A1:A19”).Find(LastDateLoaded, searchdirection:=xlNext).Row ALWAYS returns 2. It should return 1.
WSwfb.Range(“A1:A1”).Find(“11/3/2021”, searchdirection:=xlNext).Row DOES return 1.
I downloaded the spreadsheet. Do not execute any of the codes, what can it be?
Hi Paulo, I’m not clear on your question.
I am new at VBA. I am trying g to figure out a way in which to find a matching value in one cell of a column. Specifically, i need to find the value in B2, and have excel look for that value throughout that column. Then, if the value is the same throughout column B, then the procedure can end. If there is a different value in column B, then i need excel to create a new worksheet.
You can use worksheetFunction.CountA and CountIf to check if the values are the same throughout the column.
I would like to know if it is possible to return the amount of result for a search. I know it is possible with excel dialog box but I don’t find information about that for VBA, do you know if it is possible?
I have a formula writing “” if the variables are not available. When copied to a new workbook those calls are empty but not really.
Is there some way to find those cells?
Hi, I downloaded your VBA find Source Code.xlsm. On the Main page there is a message – point 2 says Follow the instructions in each module to run the code. But there are no instructions. Looking in the actual modules I see they are all supposed to run by Control+G, but although something seems to happen nothing is displayed. I suppose I am missing out some simple thing I should be doing.
Click in the sub and press F5 to run the code. The results will appear in the Immediate Window.
I am fairly sure that the wildcard character “*” represents ZERO or more characters, and not one or more characters as you have currently documented. I.e., “Apple*” will match “Apple”.
Hi Paul — Also, perhaps consider documenting “?” as well? It matches exactly one character. I’m not sure if there are any other wildcards.
I’ve been using Excel for many years to build financial models, and have only just now decided to dip my toes into VBA – a long overdue step.
I was trying to get a Chart’s visibility to toggle true/false based on a cell that has an on/off switch in it, the cell simply is set to either the word On, or Off. Your excellent video https://youtu.be/TYyPrqqFgVg for changing the colours of cell feels like where I should be heading, however for the life of me I can’t edit your code to get it working. I’ve got a basic macro working to turn it on/off, but can’t tie in the dynamic aspect. 2 hours of googling feels like enough….
Wondering if you can help at all, and also, do you have any suggestions where a good starting point would be. I purchased Benninga’s Financial Modeling – however not sure if that’s the best starting point. Any thoughts would be really helpful, and of course, appreciate if you’re too busy.
Thanks for providing the YouTube content – have found your vids to be clearer than most.
I’m having trouble doing a more specific search. I’m wanting to find the rows where a power supply is located, where the value of the cell always starts with “PS”. A cell may look like “PS 90-264VAC 12VDC 20W”. With that, there are other cells that contain the letters “PS”, like “CLAMPS”, “PHILLIPS SCREW”, “KEPS NUT”, etc. that I don’t want to be returned from my search.
Is there a way to search for “PS” And “VAC” And “W”, or a way to search so that “PS” must be the first two characters?
You can use wildcards like * and ?
PS* – All strings that start with PS.
*PS – All string ending in PS
thanks a lot
I need to know how to use the method::
Is there a way to set the ‘Within’ parameter in the ‘Find and Replace’ dialog box using VBA?
I found out about an Excel bug the hard way. If I happen to have my ‘Within’ parameter set to ‘Workbook’, the following command will replace all occurrences of “Apples” in all the worksheets (it ignores the Worksheets(1) range and uses the ‘Within: Workbook’ parameter
Worksheets(1).Rows(10).Replace What:=”Apples”, Replacement:=”Oranges”
If you can confirm the same issue, maybe you can add this warning as well in your guide.
Thanks for an extensive guide with clear examples.
In the VBA Find Parameters, do we have a parameter for “Look Within” so that one can specify whether to look in the workbook or the worksheet?
This article on the Find command was the best I found in my search. Very well done !
Question, you state in the beginning of the article the following:
“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.”
Does this hold true just for the Find command in the module or is this true for the entire project. For example, if in one module I may have searched for something in Comments. In another for a value in a cell. If I exit one module and code takes me into another module, will the parameter set in the previous module follow me into the next module ?
Thank you for your help.
Unless I’m missing something, you don’t mention this real world situation: FIND all values in a column that contain x then FIND all values within that first set x of found values that contain a string y.
FIND X —> then FIND y within x.
How would you use FIND with Boolean operators to find all values
between the ‘OR’ operator
and all values with two or more parameters,
‘last days’ AND ‘time of the end’
(This is a very common problem, yet I have not seen any solution from anyone anywhere at any time.)
I love your content, I wish I had found it sooner. There is one point I am confused on, you say above:
“Important Note: When a cell has text only, this text is considered a formula AND a value. See the table below for details”
And in your code for finding the last cell: “FindLastCell(rg As Range) As Range”, you use the constant LookIn:=xlFormulas. Your code works of course but leaves me confused. All the documentation I find says that using xlFormulas will constrain the Find method to only look in cells that have formulas. Why did you use Lookin:=xlFormulas instead of Lookin:=xlValues and why does your code work? Thanks