Find (vba) Returning Second Occurance before First

[caddcop]

I am trying to develop a macro to build formulas on a second sheet from data in cells of the first sheet.
There are header rows written into merged cells which I need to read and save their addresses.
By making a range that is wide enough to span the widths of the merged cells, I can find the headers, but for some reason, it returns the second, third, fourth, etc. occurrences before looping back to the top to find the actual first occurrence.
Since I really need these to maintain their order, this is not acceptable.
My search string is "CATEGORY ?00" to find all cells that have CATEGORY 100, CATEGORY 200, ..., CATEGORY 900 which is in the merged cells of columns A-J. They start at $A$1:$J$1, but my routine always returns that address as the last address.
I did one test where I inserted a blank row as the first row in the sheet, but since I am trying to work with a previously created "family" of spreadsheets, I would rather get this working with the sheets as is.
Not to mention understanding why I cannot get this to function as I expect or understand the logic in the code.
Thanks.

 

[Jerry Sullivan]

The problem might be with what you are using for the After:= parameter of the Range.Find function.

As an example, if you use these parameters together....
After:=Range("A1")
SearchDirection:=xlNext
SearchOrder:=xlByColumns

Then the first cell that find looks at is A2,not A1. Excel will work through the entire range before returning to check cell A1.

So to find your matches in order beginning with the first cell of the range, use the last cell of the range as the After:= Parameter.

This code will find all cells in the ActiveSheet beginning with A1.

Sub Find_Starting_with_A1()
    Dim cFound As Range
    Dim sFirstAddr As String
    
    With ActiveSheet
        Set cFound = .Cells.Find(What:="CATEGORY?", _
            After:=.Cells(Rows.Count, Columns.Count), _
            LookAt:=xlPart, SearchOrder:=xlByColumns, _
            SearchDirection:=xlNext, MatchCase:=False)
        If Not cFound Is Nothing Then
            sFirstAddr = cFound.Address
            Do Until cFound Is Nothing
                Debug.Print cFound
                Set cFound = .Cells.FindNext(After:=cFound)
                If cFound.Address = sFirstAddr Then
                    Exit Do
                End If
            Loop
        End If
    End With
End Sub

 

[caddcop]

Nice example and explanation.
What I ended up doing, was using the returned range as a collection, using "for each" to read each one and get what I needed, since the ranges are returned in order. But I will definitely file your code away for future use. Thanks again.

 

[Jerry Sullivan]

Good to hear you found that work around. Find is much more efficient than For each ...Next.

The difference won't be noticable for small data sets, but you'll want to revisit that if you have large data sets and experience a lag in response time.

Cheers!