baffling find method problems

[John]

In the example below there is a "2" in cell A1 and cell A3. If I define
field as Range("a1:c1") and searchorder as Xlbyrows then it finds the
"2" in cell a1 first. However, if I define Field as Range("A1:C3")the
Find skips cell a1 and finds the "2" in cell a3 first. Then a findnext
will find the 2 in cell a1.

I have discovered that if I define the range one row above what I really
want then it will get the first occurance in the correct cell.

thus: Range("a6:c9" starts searching in cell a7. (Assuming the After: is
a6 which is the default)

If this is true... how do you get it to start a search in cell a1?

Again, if this is just the way it works it means the Find method looks
in upper left cell last unless it's looking in just 1 row. This is
pretty confusing.

I guess my question is, is this the way Find method is supposed to work
or am I doing something wrong?

Thanks

John



Set Field = Range("A1:C1") ... or Range("A1:C3")

Set First = Field.Find(What:="2", LookIn:=xlValues, After:=Cells(1, 1),
lookat:=xlPart, searchorder:=xlByRows, Searchdirection:=xlNext,
MatchCase:=False)

FoundFirst = First.Address

 

[Wigi]

Yes, this is the way it works.

Change the After argument if you want to have it differently.

 

[Jim Thomlinson]

Because you specify the after you start looking after cell A1. I would
suggest that you just omit that parameter at which point it will start at the
beginning of the range.

Set Field = Range("A1:C1")

Set First = Field.Find(What:="2", _
LookIn:=xlValues, _
LookAt:=xlPart, _
searchorder:=xlByRows, _
Searchdirection:=xlNext, _
MatchCase:=False)

If FoundFirst Is Nothing Then
MsgBox "Not found"
Else
FoundFirst = First.Address
...
End If

 

[Otto Moehrbach]

John
To search the range starting with the first cell you have to tell it to
start AFTER the last cell. Say that you have a range defined as
"Range("A1:C3")" and you want do a FIND in it. In the find command you have
a term "After:=" and you're supposed to tell it some cell. Obviously, if
you say "Range("A1") then it will look at the second cell first. So specify
After:=Range("A1:C3)(Range("A1:C3").Count). HTH Otto

 

[John]

Jeez, dhuuu, why didn't I think of that. thanks
John