The Characters object represents a range of characters within the cell ( m圜ell). We then used this information (starting and ending position of the search string) inside the Characters object. To get the ending position of the string we used the length of the search string itself ( Len(searchString)). This gives the starting position of our search string. This means “ return the position of searchString in m圜ell.Value”. WorksheetFunction.Find(searchString, m圜ell.Value) The Find function lets you find a search string in another given string and returns the position of the string in the cell’s contents. That means, the WorksheetFunction.Find function works the same way as Excel’s FIND function. So you can use the functions under this object the same way you would use regular functions in Excel. The WorksheetFunction object is used as a container for Excel worksheet functions that can be used in VBA. If it did, then we used the WorksheetFunction.Find function to find the starting and ending position of the search string in the cell’s contents. We then used a for-loop to traverse over each cell ( m圜ell) in the range and we checked if the cell contained the search string. We used an InputBox to get the user’s search string input and we stored this text in the variable searchString. In this code, we stored the selected range of cells in the variable Rng. In our example, we entered the word “ King”. Type your search string in the input box. You will see a message window that asks you to enter your search string.M圜ell.Characters(WorksheetFunction.Find(searchString, m圜ell.Value), Len(searchString)).Font.Bold = True This can be achieved using the following module code: 'Code by Steve from Now, what if you only want to make the particular search string bold, instead of the entire cell?įor example, you might have a lot of data in your worksheet and you might want to bold only a particular word so that it stands out. How to Find and Bold Specific Text in a Cell using VBA If it did, then we made the contents of the cell bold. We then used a for-loop to traverse over each cell ( m圜ell) in the range and we checked if the cell contained the search string (using the InStr function). In this code, we again stored the selected range of cells in the variable Rng. In our example, we entered the word “King”. Copy the above code to your Visual Basic module window.Select the range of cells that you want to work with.SearchString = InputBox("Please Enter the Search String") This can be achieved by the following code: 'Code by Steve from Now consider a scenario where you need to highlight all the cells that contain a specific text using VBA. Then we used a for-loop to traverse over each cell ( m圜ell) in the range, making the contents of each selected cell bold, using the line: m圜 = True How to Bold Cells Containing a Specific Text using VBA Range("B1") = myIEDoc.getElementById("priceblock_ourprice").In this code, we stored the selected range of cells in the variable Rng. 'Then we'll get something from teh inner page content by using the ID 'Now lets read the HTML content of the page 'We wait for the Explorer to actually open the page and finish loading 'Now we open the page we'd like to use as a source for information 'if you want to see the window set this to True Set myIE = CreateObject("InternetExplorer.Application") In this example we use Internet Explorer to open a product page on Amazon and we extract the page title & product price and add the data to our spreadsheet. Everything is well commenced out so it should be really easy to understand. This code is written strictly for learning proposes and should be used as such. This is a basic VBA code to get started with InternetExplorer.Application object and work with web browsers and data extraction. Google Tag Manager Training Class – Chicago, IL.Google AdWords Classes (PPC Advertising).Python Introduction Class – Programming.Bootstrap Classes – Responsive Web Design.CSS Media Queries for Responsive Web Design.WordPress Training – Build While You Learn.Microsoft Excel Automation – Automate Repetitive Tasks.On-Site Microsoft Excel VBA Training – Introduction to Macros.On-Site Microsoft Excel Class – Advanced.On-Site Microsoft Excel Class – Intermediate.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |