Clarification on Find and FindNext methods

[hemantparmar]

Hi,

Pls help me understand this behavior of Find method in my code.

This code searches a contacts table for one or more occurrence of a Customer name and picks corresponding contact names and populates a listbox with found contacts.

When I step into this code to test for the customer (Mergermarket), i noticed that with the first Find statement the cell found is B5 and not B2 (Pls see attached image). Why would it not find B2 first? B2 gets found later with FindNext statement.

Please Login or Register  to view this content.

[JLGWhiz]

The Find statement has another optional attribute, not shown in the code above, which allows for a starting point 'After" a specified cell reference within the search range. If you want the search to begin in the first cell of the range, then set the 'After' reference to the last cell of the range. For Example if 'tcContacts_Cust' refers to $B$2:$B$100 then the statement would be:

Please Login or Register  to view this content.

[hemantparmar]

Thanks for your reply. I was under the impression that the Find statement would start search from the first cell in the range. Instead it starts the search after the first cell.
https://docs.microsoft.com/en-us/off...6)%26rd%3Dtrue

The last para in the description of After parameter is this:
If you do not specify this argument, the search starts after the cell in the upper-left corner of the range.


That's why cell B2 was not found first. So I changed the range 'tcContacts_Cust' to include the column header, which solves the problem. Now It starts the search from the second cell which is the actual data range.

[JLGWhiz]

Yep, works either way, extend the range upward or use the last cell in the range for the 'After' parameter if it is not finding the first occurrence first.
Thanks for the feedback, and don't forget to mark the thread as solved.
Regards, JLG