Please note javascript is required for full website functionality.
MVP

Blog

VBA Blog: Finding the Part of a Whole

1 June 2018

Last week we looked at using the After, SearchDirection and SearchOrder parameters of the Find method.

 

What if we needed an exact match? There’s a few things we could match.

  1. Matching the complete word
  2. Matching the case of the word (i.e. the capitalisation)
  3. Matching a specific cell formatting

Luckily, the Find method has parameters that can help us with that! Today’s article will look at the first option. Not to worry, we will cover the other options in later weeks!

The word field has been adjusted slightly to give us more interesting things to search (the changed squares are highlighted in yellow):

 

Matching the complete word

If we were to search “encounter” from C5 what would the result be? Let’s give it a go:

Sub EncounterOfTheFirstKind()

    Dim searchRange As Range

    Set searchRange = Range("A1:E10")

    Dim foundrange As Range

    Set foundrange = searchRange.Find("encounter", After:=Range("C5"))

 

    If foundrange Is Nothing Then

        Debug.Print "not found!"

    Else

        Debug.Print foundrange

        Debug.Print foundrange.Address

    End If

End Sub

It technically found ‘encounter’ but within ‘rencounter’. To force it to match the complete word, we would need to trigger the LookAt parameter. By defining it to be xlWhole, that will force it to look at the entire contents of the cell. 

Sub FindWholeWord()

    Dim searchRange As Range

    Set searchRange = Range("A1:E10")

    Dim foundrange As Range

    Set foundrange = searchRange.Find("encounter", After:=Range("C5"), LookAt:=xlWhole)

    If foundrange Is Nothing Then

        Debug.Print "not found!"

    Else

        Debug.Print foundrange

        Debug.Print foundrange.Address

    End If

End Sub

This change will land on the expected answer of cell A3.

Now, this is where things get interesting. If we were to run another search immediately after without the LookAt:=xlWhole portion, it would still maintain the parameter setting. This is because Excel saves this setting in the Find dialogue. Opening the dialogue up, view the advanced settings by clicking the "Options > >" button:

There it is! So whenever doing a search using the Find method in VBA, ensure that you always set the parameters for each Find because otherwise it will lead to unexpected results.

Come back next week where we will keep finding things on a Case-by-Case basis!

Newsletter