VBA Blogs: 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!
