CODEWITHSUNDEEP
excelvba
Bezmir
Bezmir

Reputation: 23

Find a string, find next...the found string is at the end of the search not at the beginning

I am using find next a string and copy/paste adjacent cells when the string is found. I need to have the first found occurrence at the beginning, it is currently at the very end so I need to sort it afterwards. Is there a way to correct the script below to have the first found address at the beginning? example

Sub test()

Dim rng1, rng2, rng3 As Range
Dim StrIn As String
Dim strAdd As String

StrIn = "something"

With Worksheets(1).UsedRange

        Set rng1 = .Find(StrIn, , xlValues, xlPart, xlNext)

        If Not rng1 Is Nothing Then
            strAdd = rng1.Address

        Set rng2 = rng1

        Do
            Set rng1 = .FindNext(rng1)
        Set rng2 = Union(rng2, rng1)

        Loop While Not rng1 Is Nothing And rng1.Address <> strAdd
    End If
End With

For Each rng3 In rng2
    Debug.Print rng3.Address
Next

End Sub

Upvotes: 0

Views: 79

Answers (3)

Bezmir
Bezmir

Reputation: 23

Ok I solved it with the Ron Rosenfeld's inspirational answer. I changed the SearchDirection:=xlPrevious and left everything as it was :)

Set rng1 = .Find(what:=StrIn, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious)

Set rng2 = Union(rng2, rng1)

enter image description here

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Start your search at the last cell.

eg:

With Worksheets(1).UsedRange
    Set lastCell = .Cells(.Row - 1 + .Rows.Count, .Column - 1 + .Columns.Count)
    Set rng1 = .Find(StrIn, lastCell, xlValues, xlPart, xlNext)
…

Upvotes: 1

urdearboy
urdearboy

Reputation: 14580

Simply swap the order that you build your Union

Set rng2 = Union(rng1, rng2)

Upvotes: 1

Related Questions