In this blog post, we take an in-depth look at the Excel VBA Find function. This function is very useful for (yes, you guessed it), finding things in a list.

The Excel VBA Find function works similar to the Find feature within Excel. Upon finding what you ask for, this function will return the range. We can then perform many actions, or return further information about the found item.

This blog post will demonstrate a few useful examples of the Find function in action.

How Does the Excel VBA Find Function Work?

It runs from a specified range and the only information the Find function needs is the value you want to search for. It does have quite a few optional arguments that can also be used.

You can see a list of these below with a brief explanantion of their use. This blog post will demonstrate some of the more useful options.

• What – The only required option. The value you want to search for.
• After – A starting cell range for the search
• LookIn – Where to search for the value – comments, formulas or values.
• LookAt – Whether you want to search for a complete match on the value, or a partial match.
• SearchOrder – You can order to search the range by rows, or by columns.
• SearchDirection – You can search the range forwards (xlNext), or backwards(xlPrevious).
• MatchCase – A True or False value as to whether you want a case sensitive search or not.
• MatchByte – A True or False value or double byte languages.
• SearchFormat – A true or False value to search by format, or not.

Performing a Simple Find

Let’s see a simple example of the FIND function in action.

We have the list of names below.

The following VBA can be used to find the name Gita and return the address of the found cell. The address (A5) is printed to the Immediate window so that we can see the result.

Dim FoundItem As Range

Set FoundItem = Range("A2:A7").Find("Gita")

Debug.Print FoundItem.Address

Beginner to Excel VBA? Enrol in our online Excel VBA course and get up to speed fast.

You can create a similar list to play around with this code and learn what else you can do with a found item.

The address is returned in this example to see it working correctly. Depending on your real-world requirements, you can return any information about that cell. Or perform actions to it.

Take the following example, where we want to write the current date to the cell in the column next to the found item.

Dim FoundItem As Range

Set FoundItem = Range("A2:A7").Find("Gita")

FoundItem.Offset(0, 1) = Date

When the Find function is used in Excel VBA, the criteria of the last search will be visible when using the Find feature in Excel.

This also applies to some of the options explained soon and is the same as how the Find dialog in Excel normally operates.

Preparing for When a Value is Not Found

There may be times when the Find function cannot find the value it is looking for. We need to write some code to handle these scenarios. Otherwise our macro will return an error message.

When the Excel VBA Find function cannot find the value, it returns the range object set to nothing.

So to handle this we can use an IF statement to test if the returned object is equal to nothing. And if so take a different course of action.

In the code below a message is shown to the user, and then the macro is aborted.

Dim FoundItem As Range

Set FoundItem = Range("A2:A7").Find("Paul")

If FoundItem Is Nothing Then

  MsgBox "The name was not found."
  Exit Sub

Else

  FoundItem.Offset(0, 1) = Date

End If

Using the LookIn Option with Find in Excel VBA

The Find function can do more than just look for the values in a cell. It can also look for text within formulas and comments.

In the image below you can see the word “Completed” has been entered into a comment for cell A4.

The following code searches for text within a comment, and prints the address of that cell to the Immediate window.

Dim FoundItem As Range

Set FoundItem = Range("A2:A7").Find("Completed", LookIn:=xlComments

Debug.Print FoundItem.Address

Performing a Partial Match When Searching for a Value

Another really useful option when working with the Find function is the ability to look for a partial match.

The Find function has an argument named LookAt which we can use to specify to look at the whole value, or just part of it.

In the list of buildings below, we could use this option to search for “Chicago”, even though it is only a part of the value of a cell.

The code below searches for “Chicago” as a partial match and prints the address to the Immediate window.

Dim FoundItem As Range

Set FoundItem = Range("A2:A7").Find("Chicago", LookIn:=xlValues, LookAt:=xlPart)

Debug.Print FoundItem.Address

Remember that previous Find criteria can remain after a user has run Find from a macro, or the dialog in Excel.

Therefore it can be a good idea to specify arguments such as LookIn:=xlValues to overwrite any previous setting such as comments, which we used in the previous example of this blog post.

Find a Cell with Specific Formatting

The Find function can also be used to find a cell based on its formatting.

So for example in the list of cities below, I want to find the “Rome” in a bold format.

To use this option, you first need to set the format separately. And then use the SearchFormat argument of the Find function.

The code below shows the bold font formatting being set, and then in a separate statement a search for that formatting is set to true.

Dim FoundItem As Range

Application.FindFormat.Font.Bold = True

Set FoundItem = Range("A2:A7").Find("Rome", SearchFormat:=True

Debug.Print FoundItem.Address

Any formatting that you set will still be there when you run the Find function again. Therefore it is a good idea to clear the FindFormat property after its use with the following statement.

Application.FindFormat.Clear

The Find function can be very useful and the various options provided with it means that we have great ability to find exactly what we want.

This blog post explored some of the more useful options. Please explore what else it can do and see how it can help you in your future macros.

Creating a hyperlink to another worksheet in Excel is easy. But if that worksheet is hidden then the hyperlink will not work. In this tutorial we provide a solution to follow a hyperlink to a hidden worksheet.

The example is that we have a “Main” worksheet with hyperlinks to the 4 other sheets of the workbook.

In the image below, you can see that the “Apples” sheet is hidden.

This is going to take a little Excel VBA to get this working. In this tutorial I will show two ways of making this hyperlink work, even when the sheet is hidden.

If you are a beginner in Excel VBA, why not speed track your learning by enrolling in the Excel VBA for beginners course.

Watch the Video – Hyperlink to a Hidden Worksheet

Excel VBA to Follow Hyperlink to Hidden Worksheet

Lets begin by opening the Visual Basic Editor by clicking Visual Basic on the Developer tab, or by pressing Alt + F11.

We need to use this code on the Worksheet_FollowHyperlink event so that it occurs when the link is clicked. We need to open the code window for the “Main” worksheet because that is where the event resides.

Do this by double clicking on the sheet in the Project Explorer window. The Title will display the currently active code window.

To access the Worksheet_FollowHyperlink event, select Worksheet from the Object list, and then FollowHyperlink from the Procedures list.

We can now enter some code inside the Worksheet_FollowHyperlink sub provided.

What is quite misleading is the name of this event. It is called FollowHyperlink but it does not actually follow the link. It is just an event that is triggered by someone clicking a hyperlink on that sheet.

We will need to use code to identify what link was clicked, unhide the required sheet, and take the user to it.

For the first example, the code below will do the trick.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim ShtName As String
ShtName = Target.Name
Sheets(ShtName).Visible = xlSheetVisible
Sheets(ShtName).Select
End Sub

This code declares a string variable named ShtName. It then assigns the name of the clicked hyperlink (the text displayed by the hyperlink) to that variable. A variable named Target is provided to us for this.

In this example the hyperlink text and the name of the worksheets is exactly the same, so this is a good idea. If the hyperlink text and worksheet name are different, then the second example is better for you.

This is then used to make the sheet visible and then select it.

This second example of code does not rely on the link text and sheet names matching and is more durable.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim ShtName As String
ShtName = Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1)
Sheets(ShtName).Visible = xlSheetVisible
Sheets(ShtName).Select
End Sub

In this example we extracted the sheet name from the sub address of the clicked hyperlink. A sub address is when you link to a location within the current file.

This was done by extracting all the characters up to the exclamation mark in the sub address. This is because exclamation marks separate the sheet name and cell address or range in Excel i.e. Sheet3!D2.

The Left function was used for this, and the Instr function located the position of the exclamation mark.

Hiding the Sheet Again When Leaving

Now that solves our problem and the purpose of this tutorial. However, you might want the sheet to automatically hide itself again when you have finished with it.

You could be leaving the sheet by following a hyperlink on that sheet, or maybe just by clicking a sheet tab at the bottom. I want some code to run, no matter the method of leaving.

This code will be placed in the Deactivate event of each worksheet.

Start by double clicking on a sheet in the Project Explorer window to open its code window.

Select Worksheet from the Object list (just like previously in this tutorial) and then Deactivate from the Procedures list.

And enter the line below inside the sub.

Private Sub Worksheet_Deactivate()
Me.Visible = xlSheetHidden
End Sub

This code refers to itself as Me. Nice simple code to make the sheet hide itself on the deactivation of the sheet.

Excel VBA enables you to take control when Excel tasks don’t quite work how you need them to, like in this example of following a hyperlink to a hidden worksheet. It is a fantastic skill to have.

In this blog post, we will create a macro to sort sheet tabs alphabetically with the click of a button.
Unfortunately Excel has no feature built-in to sort sheet tabs. But we can create our own, and with VBA set it up however we like.

This macro will be quite simple, but if you are not very familiar with Excel VBA you can sign up for the online Excel VBA course for beginners. This will help to get a better grasp of some of the techniques involved.

Watch the video below to see the macro being written and then tested, or read on to get the code and a written description.

Watch the Video

[Read more…] about Sort Sheet Tabs Alphabetically – Excel Macro

Using message boxes in Excel VBA is an essential skill for a budding VBA learner. By using the Excel VBA MsgBox function, you can communicate with the user by displaying messages to them, ask them questions and capture their responses.

In fact, the Excel VBA MsgBox function (message box) is probably the most popular function in Excel VBA, and one of the first you learn.

It quickly enables VBA beginners to be able to interact with the user of the macro.

This blog post will explain how to use the MsgBox function and show a few examples of it in action.

[Read more…] about Excel VBA MsgBox: Complete Guide with Examples