.Find method not searching in order

Question

Can anyone explain why the string in D2 is being built out of order in the first loop?

This is only happening for the first search value, Dom. The rest of the strings are being built in the order in which they appear (see Column B). I treid adding SearchDirection:= xlNext but the output remained the same with or without that bit of code.

In the photo, Column A:B are the raw data and Column C:D are the output from macro.

The cell in question is D2. It should show USD/EUR/GBP instead of EUR/GBP/USD

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim FoundName As Range, SearchRange As Range, Names As Range, Name As Range
Dim MyString As String, i As Long

ws.Range("A1:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Range("C1"), Unique:=True

Set SearchRange = ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
Set Names = ws.Range("C2:C" & ws.Range("C" & ws.Rows.Count).End(xlUp).Row)

For Each Name In Names
    Set FoundName = SearchRange.Find(Name, SearchDirection:=xlNext)
        For i = 1 To Application.WorksheetFunction.CountIf(SearchRange, Name)
            MyString = MyString & FoundName.Offset(, 1) & "/"
            Set FoundName = SearchRange.FindNext(FoundName)
        Next i
    Name.Offset(, 1) = Left(MyString, Len(MyString) - 1)
    MyString = ""
Next Name

Answer 1

According to Microsoft documentation about the Range.Find method, the After parameter is:

The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface. Notice that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn't searched until the method wraps back around to this cell. If you do no specify this argument, the search starts after the cell in the upper-left corner of the range.

(Emphasis mine)

---

In your code, you set the range you're searching like:

Set SearchRange = ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)

which means that the first cell actually searched will be Range("A3"). There are two ways of fixing this:

1. Expand the search range to include "A1", so the default start is "A2"
2. Specify the After parameter as the last cell in the range. Since the search wraps back around to the first cell after reaching the last cell.

---

In your scenario, I believe the simplest solution would be (1). This can be done by simply adjusting your code line to read:

Set SearchRange = ws.Range("A1:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)