Range.Find run a second time on the same range finds second occurance intead of first

[ntm91307]

I'm doing some fairly intensive searching for specific data on a sheet. So far it's involved filtering the sheet on several criteria, copying the visible rows to a TempWorksheet and then performing Range.Find looking for one of two possible values in these results. I've come across something peculiar I thought I'd share and get some opinions on.

The first .Find is looking for a specific value in a column/range ie:

Set rngRegion = .Range(B1:B6).Find(what:=strRegion, Lookin:=xlValues, Lookat:=xlWhole)

If this value is not found (rngRange is Nothing) another .Find is set off:

Set rngRegion = .Region(B1:B6).Find(what:=Empty, Lookin:=xlValues, Lookat:=xlWhole)

The business logic behind this is "Look in this table and see if we're supposed to do something with the equipment we've identified in our filtered results for this specific region (strRegion). If so, book it. If not found then look in that very same filtered dataset and see if we have a value of Empty in the Region column. Empty means we act on all 7 regions....not just this one. If we find it book them for ALL regions, otherwise we do nothing for this group and move on to the next".

After stepping the code and watching the TempWorkseet I find that if the first .Find fails and the second one is executed on a range where the FIRST occurance of region (.Cells(1,2).value) is Empty Excel returns the row for the SECOND. If however the first occurance is anywhere but Row 1 Excel returns the correct row#.

I've searched for a way to do some sort of reset on Find to clear any buffers that might be throwing off subsequent searches but I've come up empty. I've programmed around it for now but I'm real curious where the bug here is....Excel or me....

Any thoughts from the group?

 

[MarcelBeug]

If you don't supply a value for find parameter 'After', the search begins after the first cell in the range, i.c. B1:B6.
My suggestion would be to add a header row, perform the search in B2:B7, After:=B1.

 

[MARK858]

Another option...

With Range("B1:B6")
    Set rngRegion = .Find(What:=strRegion, After:=.Cells(.Rows.Count), _
                          LookAt:=xlWhole, LookIn:=xlValues, searchdirection:=xlNext)
End With

 

[ntm91307]

Which means the top cell is bypassed altogether....very clever of Excel....I suppose...

My work around tests that top cell before executing the next .Find statement so I guess that will work.

Thank you sir....

 

[ntm91307]

Very nice! Took me a minute to see what you did there but I get it. You're setting the Search to begin at the bottom of the range and letting it wrap around to the top cell as it's first stop along the way. I like the elegance....

Of course I'd love to hear the MS Excel devlopers explanation for why this had to be in the first place but I'm not going to lay awake nights over it.

Thanks for the tip...

 

[MARK858]

...You're setting the Search to begin at the bottom of the range and letting it wrap around to the top cell as it's first stop along the way....MS Excel devlopers explanation

You're welcome, the word After is important as they need a starting cell. Please see the below from Microsoft about it works.

The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface. Notice that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn’t searched until the method wraps back around to this cell. If you do no specify this argument, the search starts after the cell in the upper-left corner of the range.

 

[ntm91307]

MARK858;

Just thought I'd let you know that I had cause to revisit this section of my code and couldn't resist implementing your solution in place of my awkward workaround.

It was too darn elegant to pass up a second time...

Thanks again....