Please note javascript is required for full website functionality.
MVP

Blog

VBA Blog: Digging Up

18 May 2018

Last week we ran into the SearchOrder parameter of the Find function. We knew that we could go right across the row or down the column. But what if we wanted to manipulate the direction of which we searched? Instead of going down the columns using xlByColumns what if we wanted to go up? We can change that direction by using the SearchDirection parameter.

The SearchDirection parameter accepts the values of xlNext which means it’s looking forwards (i.e. down columns and to the right of rows) or its opposite xlPrevious.

Let’s see how it works in our amended subroutine.

Sub FindAfterByGoingUpColumns()

    Dim searchRange As Range

    Set searchRange = Range("A1:E10")   

    Dim foundrange As Range

    Set foundrange = searchRange.Find("up", After:=Range("C5"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)

    If foundrange Is Nothing Then

        Debug.Print "not found!"

    Else

        Debug.Print foundrange

        Debug.Print foundrange.Address

    End If

End Sub

Results in:

Changing the SearchOrder to xlByRows will force the Find method to go left across the rows first before going up.

It’s simple to go after what you are looking for in any direction.  Next week we’re going to be experimenting with more parameters of the Find function.

Ferret out more right here next week!

Newsletter