How To Search In Website Using Excel Vba
"I know well what I am fleeing from but not what I am in search of" – Michel de Montaigne
Contents
- 1 Introduction
- 2 Download the Source Code
- iii What is the VBA Find Function?
- 4 Introduction
- 4.ane Excel Detect Dialog
- 4.2 How to Use Options With Find
- 5 VBA Find Parameters
- five.1 Important Notation about Find Parameters
- 6 The Find Return Value
- seven How to do a Simple Find
- 7.1 When the Value is not Found
- 8 Using Later with Find
- 8.i Example 1 Without After
- 8.2 Case 2 Using Later
- eight.3 Instance 3 Wrapping Effectually
- 9 Using LookIn with Find
- 10 Using LookAt with Find
- 11 Using SearchOrder with Detect
- 12 Using SearchDirection with Find
- 12.ane Using xlPrevious with Afterward
- xiii Using MatchCase with Find
- 14 Using MatchByte with Find
- xv Using the WildCard
- 16 Using SearchFormat with Find
- 16.1 Using Wild Card with Format
- 16.ii Important – Immigration Format
- 17 Multiple Searches
- xviii Finding the Concluding Cell Containing Data
- 19 Finding Cells with Patterns
- 20 An Culling to using VBA Notice
- 21 Find and Replace
- 22 What's Adjacent?
Introduction
This mail covers everything you lot need to know about the VBA Observe function. It explains, how to use Discover, in uncomplicated terms. It besides has tons of code examples of Discover you tin can utilize right at present.
If yous want to go straight to an example of Discover so cheque out How to practise 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 cavalcade with data then go to Finding the Last Cell Containing Data
Download the Source Code
What is the VBA Notice Function?
The Find role is very ordinarily used in VBA. The three well-nigh important things to know about Find are:
- The Find function is a fellow member of Range.
- It searches a range of cells containing a given value or format.
- It is essentially the same equally using the Find Dialog on an Excel worksheet.
Introduction
Excel Detect Dialog
To view the Excel Find dialog, go to the Abode ribbon and click on Observe & Select in the Editing section. In the menu that appears select Notice(shortcut is Ctrl + F)
When you lot exercise this the following dialog will appear:
The VBA Find function uses most of the options you can run across on this Dialog.
How to Employ Options With Find
To utilize the options you pass them every bit parameters to the Find role. 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 apply it.
The VBA Find uses parameters in the same way. Y'all must give information technology the detail yous 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 and so Find will use the existing settings. We'll see more most this presently.
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 post-obit tables shows all the Find parameters.
| Parameter | Type | Description | Values |
|---|---|---|---|
| What | Required | The value you lot are searching for | Whatsoever VBA data type e.chiliad String, Long |
| Afterwards | Optional | A single prison cell range that you showtime your search from | Range("A5") |
| LookIn | Optional | What to search in e.m. Formulas, Values or Comments | xlValues, xlFormulas, xlComments |
| LookAt | Optional | Look at a part or the whole of the prison cell | xlWhole, xlPart |
| SearchOrder | Optional | The order to search | xlByRows or xlByColumns. |
| SearchDirection | Optional | The management 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 | Let searching by format. The format is set using Awarding.FindFormat | True or False |
Important Note almost Detect Parameters
Keep the post-obit in listen as information technology tin cause a lot of frustration when using Find.
As you can run into from the tabular array most of the VBA Find parameters are optional. Every bit we said earlier, if y'all 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 merely. The next time you run Find(either from the Dialog or from VBA) the existing LookIn setting will be Comments.
The post-obit code shows an example of this
' Search in comments only Range("A1:A5").Find "John", LookIn:=xlComments ' Will search comments equally 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 institute then Observe returns the prison cell with the value. That is, it returns a Range type of one cell.
If the search item is not establish so Find returns an object set to Naught.
In the following examples, you will see how to deal with the return value.
How to practise a Unproblematic Notice
Allow's start with a simple example of the VBA Discover. You need three things when using the Find function
- The Range to search
- The value you are searching for
- The Range to shop the returned cell
Let'southward take the following sample information
Nosotros are going to search for the text "Jena" in the cells A1 to A5.
The following lawmaking searches for "Jena". When information technology finds "Jena", it then places the cell in the rgFound variable.
' Discover the proper name Jena in the range A1:A5 Dim rgFound Equally Range Prepare rgFound = Range("A1:A5").Find("Jena") ' Print cell accost to Immediate Window(Ctrl + K) Debug.Print rgFound.Address
The to a higher place code shows the most bones search y'all can do. If this is your showtime fourth dimension using the VBA Find function and then I recommend you practice with a unproblematic example like this.
If you want to try these examples you tin download the workbook from the pinnacle of this post.
When the Value is not Found
When you use the VBA Find function, there will be times when yous do non discover a match. You need to handle this in your code or y'all will become the following error when you endeavour to use the returned range
The following code volition 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 non constitute Debug.Impress rgFound.Accost
What we need to do is cheque the return value like the following lawmaking shows
Gear up rgFound= Range("A1:A5").Notice("John") If rgFound Is Nothing Then Debug.Impress "Name was non plant." Else Debug.Print "Name found in :" & rgFound.Address Stop If
Using After with Detect
The Afterwards parameter is used if you desire to first the search from a particular cell. When, the Excel Find Dialog is used, the agile cell is considered the Later on cell. In other words, this jail cell is the starting point for the search. In VBA, if no Subsequently parameter is specified then the search starts at the top-left cell of the range.
Case one Without After
Allow'south look at the following code.
Set cell = Range("A1:A6").Discover("Rachal")
Find will return the jail cell A2 equally this is where the offset "Rachal" is found.
Example two Using After
In the side by side example, we use later. We are telling VBA to kickoff the search for "Rachal" after jail cell A2
Gear up cell = Range("A1:A6").Find("Rachal", After:=Range("A2"))
This will return the prison cell A6
Case 3 Wrapping Around
If a match is not establish and so the search volition "wrap effectually". This means information technology will go back to the beginning of the range.
In the following case, we are looking for Drucilla. We start our search After prison cell A2. Notice volition search from A3 to A6 and then will motion to A1.
So the following code will return A1 as there is no text "Drucilla" from A3 to A6:
Prepare prison cell = Range("A1:A6").Discover("Drucilla", After:=Range("A2"))
The search club for this example was A4, A5, A6, A1.
You tin try these example for yourself by downloading the workbook from the top of the post.
Using LookIn with Discover
Using LookIn allows you lot 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 tree | Value only |
| =LEFT("Apple tree",4)' | Appl | Formula only |
We are going to use the following sample data.
A2 Contains "Apple" as a value only
A3 Contains "Apple" every bit a formula only
A4 Contains "Apple" in the comment just
The lawmaking beneath searches for "Apple tree" in the different types: value, formula, threaded comment and notation.
To see a working example of this code you can download the source code from the peak of this mail.
' Searches in value, formula, threaded comment and note. ' https://excelmacromastery.com/excel-vba-observe/ Sub UseLookIn() ' Finds A2 Dim rgFound Every bit Range Set rgFound = shLookin.Range("A1:A5").Find("Apple", LookIn:=xlValues) Debug.Print "Found 'Apple tree' as value in: " & rgFound.Accost ' Finds A3 Set rgFound = shLookin.Range("A1:A5").Discover("Apple", LookIn:=xlFormulas) Debug.Impress "Establish 'Apple' as formula in: " & rgFound.Address ' Finds A4 Ready rgFound = shLookin.Range("A1:A5").Notice("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.Impress "Found 'Apple tree' every bit note in: " & rgFound.Address Terminate Sub
Important note that I have used xlCommentsThreaded for the tertiary one as threaded comments are used in Office 365. If you are using an older version that doesn't have threaded comments and so use xlComments.
Using LookAt with Observe
Using the LookAt function is pretty straightforward.
- xlWhole ways the search value must match the entire cell contents.
- xlPart ways the search value only has to match part of the cell.
The following example has "Apple" as role of the cell contents in A2 and it is the full contents in cell A3.
The first Find in the post-obit code finds "Apple" in A2. The second Find is looking for a total match so finds A3.
' https://excelmacromastery.com/ Sub UseLookAt() Dim cell Equally Range ' Finds A2 Set cell = Range("A1:A3").Discover("Apple", Lookat:=xlPart) Debug.Impress cell.Address ' Finds A3 Set prison cell = Range("A1:A3").Find("Apple", Lookat:=xlWhole) Debug.Print prison cell.Address End Sub
You tin can try these example for yourself past downloading the workbook from the top of the postal service.
Using SearchOrder with Find
The SearchOrder parameter allows us to search past row or by column. In the post-obit sample data we have 2 occurrences of the text "Elli".
If we search by row we will observe the "Elli" in B2 outset. This is because we search in the social club row i, then row 2 etc.
If we search by column we volition discover the "Elli" in A5 beginning. This is because we search in the order cavalcade A, the Cavalcade B etc.
The following lawmaking shows an example of using the SearchOrder with this sample data
' https://excelmacromastery.com/ Sub UseSearchOrder() Dim cell As Range ' Finds B2 Set up prison cell = Range("A1:B6").Find("Elli", SearchOrder:=xlRows) Debug.Print jail cell.Address ' Finds A5 Gear up cell = Range("A1:B6").Find("Elli", SearchOrder:=xlColumns) Debug.Print cell.Address Terminate Sub
Using SearchDirection with Find
SearchDirection allows yous to search forward or backward. Then imagine you accept the range A1:A7. Searching using xlNext will get in the gild
A1, A2, A3, A4, A5, A6, A7
Searching using xlPrevious volition go in the club
A7, A6, A5, A4, A3, A2, A1
Using xlNext with the sample data volition return A2 equally this where information technology finds the first match. Using xlPrevious will return A6.
' Annotation: Underscore allows breaking up a line ' https://excelmacromastery.com/ Sub UseSearchDirection() Dim cell As Range ' Finds A2 Set jail 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 prison cell.Address End Sub
Using xlPrevious with After
It y'all apply the Later parameter with xlPrevious then it will start earlier from the Later cell. So if we set the After cell to be A6 then the search club will be
A5,A4,A3,A2,A1,A7,A6.
The post-obit code shows an example of this
' https://excelmacromastery.com/ Sub UseSearchDirectionAfter() Dim prison cell As Range ' Finds A2 Set jail cell = shData.Range("A1:A7").Find("Elli" _ , After:=Range("A6"), SearchDirection:=xlPrevious) Debug.Impress cell.Address ' Finds A6 Fix cell = shData.Range("A1:A7").Discover("Elli" _ , After:=Range("A7"), SearchDirection:=xlPrevious) Debug.Impress cell.Address End Sub
Using MatchCase with Detect
The MatchCase parameter is used to determine if the case of the letters matters in the search. It can be ready to True or False.
- True – the case of the letters must match
- False – the case of the letters does non matter
The following sample list has 2 entries for "Elli". The 2nd has a small letter e
The following code examples prove the result of setting MatchCase to Truthful and False
' https://excelmacromastery.com/ Sub UseMatchCase() Dim cell As Range ' Finds A2 Ready prison cell = Range("A1:B6").Notice("elli", MatchCase:=False) Debug.Print cell.Accost ' Finds A6 Ready cell = Range("A1:B6").Discover("elli", MatchCase:=True) Debug.Print prison cell.Accost End Sub
Using MatchByte with Find
The MatchByte parameter is used for languages with a double-byte character ready. These are languages such equally Chinese/Japanese/Korean.
If you lot are not using them then this parameter is not relevant. They are used as follows
- True means to match simply double-byte characters with double-byte characters.
- False means to double-byte characters can match with single or double-byte characters.
Using the WildCard
Nosotros tin can apply the asterisk symbol(*) as a wild menu when searching for text. The asterisk represents ane or more than characters.
For case
"T*" will find any word that starts with T.
"To*" will notice any discussion that starts with To.
"*y" volition detect any word that ends with y.
"*ey" will find any give-and-take that ends with ey.
The lawmaking below shows examples of using the wildcard based on this information:
' Examples of using the wild carte du jour ' https://excelmacromastery.com/excel-vba-discover/ Sub WildCard() Dim rgFound Equally Range ' Finds Tom in A2 Set rgFound = shWildCard.Range("A1:A6").Find("T*") Debug.Impress 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 Gear up 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 prison cell " & rgFound.Address ' Finds Helen in A6 Set rgFound = shWildCard.Range("A1:A6").Find("*elen*") Debug.Impress rgFound.Value & " was establish in jail cell " & rgFound.Address End Sub
Using SearchFormat with Find
Search Format is a flake different than the other parameters. Information technology allows you to search for a jail cell format such as font blazon or jail cell colour.
You need to fix the format offset by using the Application.FindFormat property. Then y'all set SearchFormat to True to search for this format.
In the following sample data, we take two cells formatted. Cell A5 is gear up to Bold and Cell A6 has the fill up colour set to red.
The following code searches for the bold cell:
' Find the cell which has a assuming format ' https://excelmacromastery.com/excel-vba-find/ Sub UseSearchFormat() Dim findText As Cord findText = "Elli" ' Clear previous formats and prepare new format Application.FindFormat.Clear Application.FindFormat.Font.Assuming = True ' Finds A2 Dim rgFound As Range Set rgFound = Range("A1:A6").Notice(findText, SearchFormat:=False) Debug.Impress "Establish '" & findText & "' in prison cell: " & rgFound.Address ' Finds A5 Set rgFound = Range("A1:A6").Find(findText, SearchFormat:=True) Debug.Print "Found '" & findText & "' in cell: " & rgFound.Address Application.FindFormat.Articulate End Sub
Using Wild Carte with Format
You tin search for a cell based on the format only. In other words, the value in the prison cell is ignored in the search. You do this past placing "*" in the search string.
The following code searches for a prison cell that is formatted – the cell colour in this example is set to red. The contents of the jail cell do not affair:
' Discover the cell which is formatted - contents do not affair ' https://excelmacromastery.com/excel-vba-find/ Sub UseSearchFormatWild() ' Clear previous formats and prepare new format Application.FindFormat.Articulate Awarding.FindFormat.Interior.Color = rgbRed ' Finds A2 as it ignores the format and finds the first cell with any contents Dim rgFound Every bit Range Ready rgFound = shSearchFormat.Range("A1:B6").Discover("*", SearchFormat:=False) Debug.Print "Found format in jail cell: " & rgFound.Accost ' Finds A5 as this is offset cell with the format set to interior colour equally red Set rgFound = shSearchFormat.Range("A1:B6").Find("*", SearchFormat:=Truthful) Debug.Print "Establish format in jail cell: " & rgFound.Accost Application.FindFormat.Clear End Sub
Of import – Clearing Format
When you lot set the FindFormat attributes they remain in identify until you lot set them again. This is something to lookout man out for.
For example, imagine you set the format to bold and then utilize Find. Then y'all fix the format to font size 12 and use Discover again. The search will expect for cells where the font is bold AND of size 12.
Therefore, information technology is a good thought to articulate the format before yous use it as I accept done in the to a higher place examples.
Application.FindFormat.Clear
You can see we used this in the 2nd SearchFormat example in a higher place.
Multiple Searches
In many cases you will want to search for multiple occurrences of the aforementioned value. To do this we employ the Discover role offset. So we use the .FindNext function to observe 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 2nd occurrences of the text "Elli".
' https://excelmacromastery.com/ Sub SearchNext() Dim cell Equally Range ' Find first - A2 Set cell = Range("A1:A9").Find("Elli") Debug.Impress "Found: " & cell.Address ' Observe 2d - A5 Set prison cell = Range("A1:A9").FindNext(cell) Debug.Print "Found: " & cell.Address End Sub
Sometimes you lot won't know how many occurrences there is. In this instance we use a loop to keep searching until we have institute all the items.
Nosotros utilize Observe to become the first detail. If we find an detail we then apply a Do Loop with .FindNext to find the rest of the occurrences.
FindNext volition wrap effectually. That is, afterward it finds A9 it volition continue the search at A1. Therefore, we store the address of the first cell nosotros find. When FindNext returns this cell again we know we have establish all the items.
The following code volition find all the occurrences of Elli
' https://excelmacromastery.com/ Sub MultipleSearch() ' Get proper noun to search Dim name Equally String: name = "Elli" ' Get search range Dim rgSearch As Range Set rgSearch = Range("A1:A9") Dim cell Equally Range Set cell = rgSearch.Find(name) ' If non found so get out If cell Is Cypher Then Debug.Print "Not found" Go out Sub Cease If ' Store first cell accost Dim firstCellAddress As String firstCellAddress = cell.Address ' Detect all cells containing Elli Practice Debug.Impress "Found: " & cell.Address Set up prison cell = rgSearch.FindNext(cell) Loop While firstCellAddress <> jail cell.Address Stop Sub
The output from this code is
Institute: $A$2
Found: $A$5
Constitute: $A$eight
Finding the Last Cell Containing Information
A very common task in VBA is finding the last cell that contains data in a row or colum. This does not utilise the VBA Find function. Instead, we use the post-obit code to find the concluding row with data
' Find the last row with data in column A LastRow = Cells(Rows.Count, 1).Finish(xlUp).Row ' Find the last row with data in column C LastRow = Cells(Rows.Count, 3).Finish(xlUp).Row
To find the last column with data we use similar code
' Find the concluding column with data in row i lLastCol = Cells(one, Columns.Count).End(xlToLeft).Column ' Find the terminal cavalcade with data in row 3 lLastCol = Cells(three, Columns.Count).End(xlToLeft).Column
Finding Cells with Patterns
If you want to discover cells with sure patterns then y'all have to use the Like operator rather than Discover.
For example, to notice 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 jail cell Every bit Range ' Go through each cell in range For Each cell In Range("A1:A20") ' Bank check the design If cell Like "[E]*" Then Debug.Print cell End If Next End Sub
To see a existent-world example of using pattern matching cheque out Instance three: Bank check if a filename is valid.
An Alternative to using VBA Notice
If y'all are expecting a large number of hits then using an array is a better option. You can read a range of cells to an assortment very rapidly and efficiently.
The following code reads the cell values to an array and then reads through the assortment 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 proper name Every bit Variant, cnt As Long ' Go through the array For Each proper noun In arr ' Count in the name 'Ray' is found If name = "Ray" And then cnt = cnt + 1 End If Next name Debug.Print "The number of occurrences was: " & cnt End Sub
Detect and Replace
To do a find and Replace you tin can use the Replace function. It is very similar to using the Find role.
The supercede function is exterior the telescopic of this postal service although a lot of what you read hither can be used with information technology. Yous can see the details of information technology at Microsoft – VBA Supervene upon Function
What'southward Next?
Free VBA Tutorial If you are new to VBA or y'all want to sharpen your existing VBA skills then why not endeavor out the The Ultimate VBA Tutorial.
Related Training: Get full admission to the Excel VBA training webinars and all the tutorials.
(Notation: Planning to build or manage a VBA Application? Acquire how to build 10 Excel VBA applications from scratch.)
Source: https://excelmacromastery.com/excel-vba-find/
Posted by: thomasprel1989.blogspot.com
0 Response to "How To Search In Website Using Excel Vba"
Post a Comment