Range.Find skips the first cell ??

[slintz]

It seems that a straight forward Range.Find() call implicitly starts
searching AFTER the first cell in the range, AS IF called as

f = r.Find(after:=r.cells(1,1),...)

This work-around is effective:

f = r.find(after:=r.cells(r.Rows.Count, r.Columns.Count),...)

but it's (obviously) fugly. Am I missing something?

[Tim Williams]

What do you want it to do instead?

The example in Help has useful code.

Tim.

--
Tim Williams
Palo Alto, CA


"slintz" <[email protected]> wrote in message
news:[email protected]...
> It seems that a straight forward Range.Find() call implicitly starts
> searching AFTER the first cell in the range, AS IF called as
>
> f = r.Find(after:=r.cells(1,1),...)
>
> This work-around is effective:
>
> f = r.find(after:=r.cells(r.Rows.Count, r.Columns.Count),...)
>
> but it's (obviously) fugly. Am I missing something?
>

[Dave Peterson]

I like to start after the last cell in the range and look for the next cell...

Something like:

Dim myRng as range
Dim FoundCell as range

set myrng = worksheets("Sheet1").range("a1:z99")

With myRng
Set FoundCell = .Cells.Find(what:="what to look for", _
after:=.Cells(.Cells.Count), LookIn:=xlValues, _
lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False)
End With

slintz wrote:
>
> It seems that a straight forward Range.Find() call implicitly starts
> searching AFTER the first cell in the range, AS IF called as
>
> f = r.Find(after:=r.cells(1,1),...)
>
> This work-around is effective:
>
> f = r.find(after:=r.cells(r.Rows.Count, r.Columns.Count),...)
>
> but it's (obviously) fugly. Am I missing something?

--

Dave Peterson

[slintz]

I want find to behave according to MY sensibilities <smile>, to wit: that
when no "After" parameter is provided, it returns the FIRST cell to match the
criteria, not the first match after the first cell.

Judging from other responses and other sources, it seems I will just have to
live with this semantic despite how much pain it causes... <sigh>


"Tim Williams" wrote:

> What do you want it to do instead?
>
> The example in Help has useful code.
>
> Tim.
>
> --
> Tim Williams
> Palo Alto, CA

[slintz]

Yeah, your solution is the same as mine - except yours is noticibly cleaner.
Despite being disgusted by this behavior I'm sure I can learn to make due
<smirk>.

Thanks for your help!



"Dave Peterson" wrote:

> I like to start after the last cell in the range and look for the next cell...
>
> Something like:
>
> Dim myRng as range
> Dim FoundCell as range
>
> set myrng = worksheets("Sheet1").range("a1:z99")
>
> With myRng
> Set FoundCell = .Cells.Find(what:="what to look for", _
> after:=.Cells(.Cells.Count), LookIn:=xlValues, _
> lookat:=xlWhole, searchorder:=xlByRows, _
> searchdirection:=xlNext, MatchCase:=False)
> End With
>