Please note javascript is required for full website functionality.
MVP

Blog

VBA Blog: 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.

Newsletter