CODEWITHSUNDEEP
excelvba
BillR
BillR

Reputation: 43

xlPart vs xlWhole

I'm wondering how Excel VBA prioritizes searches when using xlPart vs. xlWhole.

The first row in my worksheet contains header text for many columns. Depending on who generates this report and what settings they use, the header text can change - both locations and content. For that reason, I'm using the .Find method with "Lookat:=xlPart" to search for substrings that I know will always be present.

The problem I'm running into is that I want to find the first (left-most) column containing the text "Job ID". Column A contains the exact text "Job ID", and column Z contains the text "Master Job ID". But this line of code always returns column Z instead of column A which is the left-most column for which that criteria should find a match:

Set FoundRange = Range(SearchRange).Find(What:="Job ID", LookIn:=xlValues, LookAt:=xlPart, _
                 SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If I change it to "Lookat:=xlWhole", it returns column A, as it should. But why doesn't it find a match in column A using "Lookat:=xlPart"?

If I change the contents of cell Z1 so there is no substring match to "Job ID", then .Find will correctly return column A regardless of whether I use xlPart or xlWhole. It seems to me that it should always return column A in this case.

Is there some subtlety that I'm missing?

Upvotes: 1

Views: 2521

Answers (1)

chris neilsen
chris neilsen

Reputation: 53166

The Find method includes an optional parameter After. This specifies where the search starts, literally at the cell after the specified cell. When not specified it defaults to the first cell in the search range.

In your example, searching a single row (row 1?) without specifying After, the search starts at B1. That's why it finds Z1 first.

To start at the first cell, specify After as the last cell in the search range

After:=SearchRange.Cells(SearchRange.Cells.Count)

Upvotes: 4

Related Questions