The Range.Find() function in VBA-Excel is quite a tricky one. In general, you may think it is straight forward, because it usually returns what you are looking for, until the moment when it simply surprises you.
This is the MSDN description of it: “Finds specific information in a range.” – 1 sentence, 6 words.
In order to see the tricky part of Range.Find() we should have the following conditions:
- the searched value should be in the first cell of the range
- the searched value should be repeated more than once
To illustrate this case, let’s consider the following two columns as two separated ranges:
If we use the code below it will give different outputs, whenever one of the two ranges on the picture are given:
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub TestMe() Dim myR As Range Dim myS As Range Set myS = Range("B1:B5") 'change the range to "C1:C5" correspondingly for the right column With myS Set myR = .Find(1) Debug.Print myR.Row Set myR = .Find(1, after:=.Cells(.Cells.Count)) Debug.Print myR.Row End With End Sub |
Which would be quite strange, because as far as on both ranges, the number 1 is on the first row, we would like to expect the first row as an answer. Still, if we pass the right hand side range, it would be a surprise that the “Find” method from VBA would return 2 and then 1.
Why is this so?
Because in the second part we specify the “After” parameter and it works well.
This is the After parameter in MSDN:
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.
Thus you give the last cell possible as a start. It knows that it should start After it. So, it theoretically has nothing better to do and simply starts from the beginning of the range. The idea of the After parameter is to set the last value that would be checked. Thus, if we have only one value it is always returned correctly, no matter of the After parameter. With more than one value, it returns the first value it finds.
Thus, in the code above, because of the usage of After the Range.Find() searches the cells in the following sequence:
B1 > B2 > B3 > B4 > B5
Without the After the sequence is the following:
B2 > B3 > B4 > B5 > B1
You probably need some time to realize it. It is worthy to run the code with the examples a couple of times. Indeed, the default parameter is not set the way you would expect. I mean, if I give a range and I look into it, I expect to check the first cell first by default. And not to check the first cell last, because it is the default parameter of After, when it is not set.
But these are my expectations. Anyhow, this is one of the reasons why I am using my own library for similar functions.
Enjoy it! 🙂