Please note javascript is required for full website functionality.
MVP

Blog

VBA Blog: After You

4 May 2018

Having stumbled upon the Find method last week, we’re going to lay fingers on some of the parameters.  Last time we did the basic search looking for a simple string within a Range.

Let’s go back to our worksheet last week:

If we were to perform a simple search for the string “up” on this Range, using the subroutine from last time, it would return “turn up” in B1. This is because the Find method starts from the upper left corner from the range.

Notice how the cursor is on C5.  If we were to press Ctrl + F in Excel and use the Find dialogue, it would start the search from that cell.

What if we wanted to find a string with “up” after the word “sight”?  How can we tell VBA to behave in the same way as the Find dialogue?

This is where the After parameter comes in handy.  After takes a Range of a single cell to use as the basis to search from.  If you give After a range that is bigger than 1x1, then it will generate the following error:

After is the second parameter of the Find method, so we can simply pass it after our search string.  We can amend our code as follows:

Sub FindAfter()

    Dim searchRange As Range

    Set searchRange = Range("A1:E10")

    Debug.Print searchRange.Find("up", Range("C5"))

    Debug.Print searchRange.Find("up", Range("C5")).Address

End Sub

This will result in:

Success!  Notice that Find went across the row, which is the default. 

Next week we will descry setting parameters in the Find function to go down the columns instead.

See you next time!

Newsletter