VBA Blogs: Lost and Found
20 April 2018
It’s easy to get lost in a sea of data, but at times we want to find something in particular, be it a word, number or formula. Autofilters can help us do that. In Excel, the Find & Select (CTRL + F) function helps us do specific value/formula searches. But how can we automate this in VBA?
Enter the Find method.
Find finds a specific information in a range, so it can only be used on Range objects. Let’s have a look at its parameters.
| Parameter | Type | Description | Values |
|---|---|---|---|
| What | Required | What we are looking for | Any VBA data type, can be a string “find me”, or integer 42 |
| After | Optional | A single cell after which you want the search to begin – will not be searched until the search loops around and returns to it. Defaults to the cell in the upper-left corner of the range. | Range(“A1”) |
| LookIn | Optional | What type of thing we want to look in - defaults to xlValues | xlValues for searching values e.g. “51” xlFormulas for searching formulae “=A5 xlComments for searching within the Review Comments |
| LookAt | Optional | Defines a complete or partial search - defaults to xlPart | xlWhole matches the entire contents of the field xlPart matches part of the field |
| SearchOrder | Optional | The search order - defaults to xlByRows | xlByRows to go across the rows first xlByColumns to go down the columns first |
| SearchDirection | Optional | The search direction – the default is xlNext | xlNext going forwards in direction xlPrevious going backwards in direction |
| MatchCase | Optional | True to make the search case sensitive - default value is False | Variant |
| MatchByte | Optional | Used in double-byte language searches and irrelevant if the language in Excel is single-byte. Double-byte languages include Korean, Japanese etc., Defaults | True to have double-byte characters match only double-byte characters. False to have double-byte characters match their single-byte equivalents. |
| SearchFormat | Optional | Searching by format which must be set first using Application.FindFormat | True or False |
Let’s start with some data:
Today we are going to start with the most basic search. Let's ignore all the other parameters and use their default settings for the time being.
Sub TestFind()
Dim searchRange As Range
Set searchRange = Range("A1:E10")
Debug.Print searchRange.Find("observe")
End Sub
We get the result:
It gives us the value of the cell that has the first match. But what if we wanted to know the address of the cell? As the Find method returns a Range result, we can simply use the Address property.
Sub TestFindAddress()
Dim searchRange As Range
Set searchRange = Range("A1:E10")
Debug.Print searchRange.Find("observe").Address
End Sub
But what happens we try to search for something that isn’t in our range?
Sub CantFind()
Dim searchRange As Range
Set searchRange = Range("A1:E10")
Debug.Print searchRange.Find("lost").Address
End Sub
We will get an error.
This is because there was no Range returned. To prepare for that contingency, a little checking is required to determine if there was a result. This where the Nothing keyword would be utilised. Nothing represents the default value of a data type. For example, a Nothing String would be “”.
As a result, if the Find method can not return a result, it will return a default value which is Nothing and our code can be adjusted as follows
Sub CantFindNothing()
Dim searchRange As Range
Set searchRange = Range("A1:E10")
If searchRange.Find("lost") Is Nothing Then
Debug.Print "lost is not found!"
Else
Debug.Print searchRange.Find("lost").Address
End If
End Sub
It doesn’t seem to be difficult to Find things using VBA!
Next week we will go over some of the parameters on the Find function and see how they fine tune your searches to pinpoint accuracy.
