.Find deterministic? Always the first one?

[jungix]

I know that we can search by rows or by column in a selection. But is it always the first answer found that is returned.

If I search for something in column A and that it is in A5, but also in A8 and A12 for instance, will the Range("A:A").Find(.... always return A5?

[lucas]

Try Findnext. Straight out of the help file:

When the search reaches the end of the specified search range,
it wraps around to the beginning of the range.
To stop a search when this wraparound occurs, save the
address of the first found cell, and then test each
successive found-cell address against this saved address.

This example finds all cells in the range A1:A500 that
contain the value 2 and changes their values to 5.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

[jungix]

Thanks Lucas but this doesn't help me. I was not clear about what I wanted the macro to do.
What I ask is if in your exemple firstadress refers to the first cell in the column (lower row index in column A containing 2).
I only want to find the first one, and no other. I need that the cell the find method returns is the first one in my range.

[lucas]

I see. I think the answer is yes. I think it always finds the first occurance from the top down....don't quote me on that though. Maybe others will give input on this.

[jindon]

jungix

Put 5 in A1 and A5 and compare following codes..

Sub smaple1()
Dim r As Range
Set r = Columns("a").Find(5)
MsgBox r.Address
End Sub


Sub Sample2()
Dim r As Range
Set r = Columns("a").Find(5,Cells(Rows.Count,"a"))
MsgBox r.Address
End Sub

[lucas]

Your right jindon, sample 1 does not find the top instance but sample 2 does. Thanks for pointing that out.

[jindon]

Lucas,

What I guess is Find starts to find from next cell like FindNext/FindPrevious do.....

[johnske]

"Find" has a quirk in that if the first cell in the search range has the required value, then that cell is always 'reported' last. One way around this is to use the last cell in the search range as the first cell for the start of the search, this means the last cell is then actually put last. (Use the "After" option in the Find function - as in the example below, where column A is the search region)...

Set cell = .Find("*", After:=Range("A" & Rows.Count).End(xlUp), LookIn:=xlValues)