baffling find method problems
 

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

baffling find method problems
 

Yes, this is the way it works.

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

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"John" wrote:

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

baffling find method problems
 

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

--
HTH...

Jim Thomlinson


"John" wrote:

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

baffling find method problems
 

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" wrote in message
...
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

baffling find method problems
 

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

Otto Moehrbach wrote:
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" wrote in message
...
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