Excel VBA Find String in Range

Excel VBA Find String in Range – This post shows three examples of how to use the Excel VBA Find method, searching a range of cells for a specific string.  This is a good technique to employ if a user would like to see where a list of strings appear in a workbook.  The samples will also highlight the background of each cell based on search results, and build a hyperlink for each search result so the user can click on the link and “Go To” the cell address.

Excel VBA Find String in Range – Example 1 – Highlight cell background based on the Find result

Let’s say you have the following list of player names, on a tab named “List”:

Excel VBA Find String in Range

You would like to search a second tab “Lookup”, to see if each of these players shows up in the detail.  The “Lookup” tab contains details about player tournaments:

Excel VBA Find String in Range

For the first example, you can use the following macro:

This macro loops through each player in the “List” tab, highlighting the cell background Yellow if the string does not exist in the “Lookup” tab, and Green if the player does exist in the “Lookup” tab:

Excel VBA Find String in Range

 

Excel VBA Find String in Range – Example 2 – List Cell Address and Hyperlink to the “First” match

Building on the first macro, the next example will add the location of the first match, along with a hyperlink so the user can “Go To” the matching cell in tab “Lookup”

Here is the macro:

This macro will add a second column with cell address and hyperlink to the first match in result of the Find method:

Excel VBA Find String in Range

If you click on the hyperlink next to “Jason Day” in cell B3, the link takes you to Tab “Lookup”, Cell “A44”:

Excel VBA Find String in Range

 

Excel VBA Find String in Range – Example 3 – List Cell Address and Hyperlink to ALL matches

Building on the second macro, the next example will add the location of the ALL matches, along with a hyperlink so the user can “Go To” the matching cell in tab “Lookup”

Here is the macro:

This macro will add multiple columns next to the search string (if applicable), with cell address and hyperlink to the first match in result of the Find method:

Excel VBA Find String in Range

You get the idea!  Modify the code to suit your needs.

These examples were created in Excel 2013.

 

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

 

 

Leave a Comment