VBA Find failure

[Guraknugen]

Test code:

Option Explicit

Sub SearchTest()
    Dim Search As Range
    Dim MyRange As Range
    
    Set MyRange = Worksheets(1).Range("A1:A8")
    
    Set Search = MyRange.Find(What:="0", LookIn:=xlValues)
    Debug.Print Search.Row
    Set Search = MyRange.Find(What:="00", LookIn:=xlValues)
    Debug.Print Search.Row
    Set Search = MyRange.Find(What:="000", LookIn:=xlValues)
    Debug.Print Search.Row
    Set Search = MyRange.Find(What:="0000", LookIn:=xlValues)
    Debug.Print Search.Row
    Set Search = MyRange.Find(What:="1", LookIn:=xlValues)
    Debug.Print Search.Row
    Set Search = MyRange.Find(What:="11", LookIn:=xlValues)
    Debug.Print Search.Row
    Set Search = MyRange.Find(What:="111", LookIn:=xlValues)
    Debug.Print Search.Row
    Set Search = MyRange.Find(What:="1111", LookIn:=xlValues)
    Debug.Print Search.Row
End Sub

Type the following in the following cells:

A1: '0
A2: '00
A3: '000
A4: '0000
A5: '1
A6: '11
A7: '111
A8: '1111

Expected result (?) in the monitor window:

 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8

Result in the monitor window:

 2 
 2 
 3 
 4 
 5 
 6 
 7 
 8

Why is the first line in the monitor 2 instead of 1, and what can I do about it?

 

[wideboydixon]

See the documentation here:

https://msdn.microsoft.com/VBA/Excel-VBA/articles/range-find-method-excel

Specifically, the "After" parameter:

If you do no specify this argument, the search starts after the cell in the upper-left corner of the range.

You can get your required output like this:

    Set Search = MyRange.Find(What:="0", LookIn:=xlValues, After:=MyRange(MyRange.Count))

etc.

WBD

 

[Guraknugen]

Thank you for replying.

See the documentation here:

https://msdn.microsoft.com/VBA/Excel-VBA/articles/range-find-method-excel

Specifically, the "After" parameter:



You can get your required output like this:

    Set Search = MyRange.Find(What:="0", LookIn:=xlValues, After:=MyRange(MyRange.Count))

etc.

WBD

Thanks, that was it.
I actually read about the After parameter on that very page, but I completely misunderstood it for some reason.
At least I found a clue to it, because I discovered that if I move all the cells one step down and search A1:A9, it also worked.

It still doesn't work when the list isn't sorted, but I found that one out myself:

LookAt:=xlWhole

Because otherwise, when searching for "0", it stops if it first finds "00", "000" or "0000".

Thank you for your help!