CODEWITHSUNDEEP
excelvba
Random_dude
Random_dude

Reputation: 78

.FindNext() with xlPrevious

I'm attempting to find all values "O" from bottom to top. I can't use the "normal" find because I'm going to offset some values down and .Find() will find them again.

The problem is that when i use .FindNext after a .Find() with SearchDirection:=xlPrevious it goes back to the top instead of following the direction.

For test purposes I'm using this code:

Dim f As Range

Set ws = ThisWorkbook.ActiveSheet

ws.Cells(7, 5).Value = "O"
ws.Cells(6, 5).Value = "O"
ws.Cells(5, 5).Value = "O"
ws.Cells(5, 6).Value = "O"
ws.Cells(5, 7).Value = "O"

Set f = ws.Range("A1:AX50").Find(what:="O", SearchDirection:=xlPrevious)
Debug.Print f.Address
Set f = ws.Range("A1:AX50").FindNext(f)
Debug.Print f.Address

It prints $E$7 and $E$5. I was expecting $E$7 and $E$6.

I'm not sure this is the best method to find all values from bottom to top. Any help would be aprreciated.

Upvotes: 1

Views: 1592

Answers (2)

huntedhippo
huntedhippo

Reputation: 1

Use .FindPrevious instead of .FindNext

Upvotes: 0

user10735198
user10735198

Reputation:

The Range.FindNext method is clearly not inheriting the SearchDirection:=xlPrevious argument. You need the base Range.Find method with the After:=f argument instead.

Dim f As Range, addr As String, ws As Worksheet

Set ws = ThisWorkbook.ActiveSheet

ws.Cells(7, 5).Value = "O"
ws.Cells(6, 5).Value = "O"
ws.Cells(5, 5).Value = "O"
ws.Cells(5, 5).Value = "O"
ws.Cells(5, 6).Value = "O"
ws.Cells(5, 7).Value = "O"

With ws.UsedRange.Cells  'Range("A1:AX50")
    Set f = .Find(What:="O", after:=.Cells(1), SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
    If Not f Is Nothing Then
        addr = f.Address(0, 0)
        Do
            Debug.Print f.Address
            Set f = .Find(What:="O", after:=f, SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
        Loop Until addr = f.Address(0, 0)
    End If
End With

Upvotes: 2

Related Questions