Please note javascript is required for full website functionality.
MVP

Blog

VBA Blog: Looking Up and Down

11 May 2018

Last week we set the After parameter for the Find method to distinguish which cell we wanted to start from. But it defaulted to go across the row. It can be set in order to go down the columns first using the SearchOrder parameter.

This is where we need to look at how VBA handles parameters.

When we type the Find method and open the brackets, we will see the tool tip for the parameter list – identical to when we use functions in Excel formulae.

The bolded parameter is the one we are currently entering. The parameters in square brackets are the Optional parameters. We could skip to SearchOrder using:

searchRange.Find("up", Range("C5"), , , xlByColumns)

But this isn’t a good approach. It is very easy (and we will admit to having done this) to put the wrong number of commas in and have the subroutine fail at that point in the code.

VBA allows us to assign values to specific parameters directly. This is done by naming the parameter followed by “:=” then the value. In the previous example that line is better written as:

searchRange.Find("up", After:=Range("C5"), SearchOrder:=xlByColumns)

With that in mind, our full subroutine will look like this:

Sub FindAfterByColumns()

    Dim searchRange As Range

    Set searchRange = Range("A1:E10")  

    Dim foundrange As Range

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

 

    If foundrange Is Nothing Then

        Debug.Print "not found!"

    Else

        Debug.Print foundrange

        Debug.Print foundrange.Address

    End If

End Sub

Which results in:

Next week we will pinpoint exactly how to dig up our column!

Newsletter