Excel VBA Range.FindNext v Range.Find - What have I missed?

Question

Question: vba Range.FindNext v Range.Find - what have I missed ?

Hi

( I am still not too familiar with posting here, so apologies if I have anything not quite right )

I’ve been doing a few VBA codes that are all variations of searching down a long column. (Often it involves looking for many occurrences consecutively, either to produce a list of them all or to select one or more ( when doing a LookAt Part type option thingy) )

I am not a computer or coding professional, and don’t have so much experience, so what have I missed? …

I was expecting some advantage from the .FindNext compared with using the .Find when doing the sort of thing that I have been doing.

Take a simplified example where I want to find the two words with rOh in them

    /      A       B        C
     1        
     2
     3             rOh3  
     4               
     5               
     6               
     7             rOh7  
     8               
     9               
    10              

So I want the Debug.Print output of

    rOh3
    rOh7

This is typical of the sort of code I have seen suggested, that is to say what I find over the internet and in tutorials:

    Sub VBAFindNext()
    Dim FirstrngFnd As Range, rngFnd As Range
     Set FirstrngFnd = Range("B1:B10").Find(What:="roh", LookAt:=xlPart)
        If FirstrngFnd Is Nothing Then Exit Sub
     Set rngFnd = FirstrngFnd
     Debug.Print FirstrngFnd.Value
        Do
         Set rngFnd = Range("B1:B10").FindNext(rngFnd)
        If Not rngFnd = FirstrngFnd Then Debug.Print rngFnd.Value
        Loop While Not rngFnd = FirstrngFnd
    End Sub

I am doing a code like this, which seems a bit simpler and more flexible…

    Sub FindTheNext()
    Dim rngFnd As Range
     Set rngFnd = Range("B1:B10").Find(What:="roh", LookAt:=xlPart)
        If rngFnd Is Nothing Then Exit Sub
        Do While Not rngFnd Is Nothing
         Debug.Print rngFnd.Value
         Set rngFnd = Range("B" & rngFnd.Row + 1 & ":B10").Find(What:="roh", LookAt:=xlPart)
        Loop
    End Sub

So I was just wondering if I have missed anything? I could write the latter code as this, but I don’t see any improvement

    Sub TheNextVBAFindNext()
    Dim rngFnd As Range
     Set rngFnd = Range("B1:B10").Find(What:="roh", LookAt:=xlPart)
        If rngFnd Is Nothing Then Exit Sub
        Do While Not rngFnd Is Nothing
         Debug.Print rngFnd.Value
         Set rngFnd = Range("B" & rngFnd.Row + 1 & ":B10").FindNext(rngFnd.Offset(1, 0))
        Loop
    End Sub

So the question: I am hoping someone with more experience or someone that understands more deeply into the workings of these things can explain any reasons that might make the first code preferable. I may be missing something important in my ignorance. I suppose the specific question is “What is the point of .FindNext”. Is it that it just saves typing the search criteria again, or is there more to it than that. I was thinking, possibly naively , that for a very long column my code might be a bit better as it looks each time at a shortened range, … but whether that is true will depend I guess on exactly what is going on “behind the scenes”, which I don’t know. Does anyone else know that?, and can they explain it in simple terms if possible.

Thanks

Alan

P.s. I have read that some people think that FindNext is broken. But I have not seen any hard details to prove that yet.

https://msdn.microsoft.com/de-de/vba/excel-vba/articles/range-findnext-method-excel https://msdn.microsoft.com/de-de/vba/excel-vba/articles/range-find-method-excel .FindNext failing after a .Find function (excel vba)

The actual codes I have are typically a lot more complicated, that is why I am trying to understand a bit more of what is going on: https://www.excelforum.com/excel-programming-vba-macros/1186516-smarter-search-process.html#post4664009

Answer 1

I think this is the nearest I can get to in the meantime to the answer..

_1) the fact that you don’t need to give the search criteria again if you use FindNext is probably not particularly of much interest.

_ 2 The fact that for FindNext somewhere VBA is remembering where the last found cell is probably not particularly advantageous. If you use .Find instead in the ways I do in the codes below then you achieve the same, and the extra text in the argument is probably useful to keep track of what is being done in a code..

_3) I suspect the FindNext might have been some attempt to help not fall into a trap as I did in my codes ( the second two in my first post..): On further experimenting I found that those two codes don’t actually do what I wanted.

For example if this is my test range:_...

    /      A       B        C
     1             rOh1
     2             rOh2
     3             rOh3  
     4               
     5               
     6               
     7             rOh7  
     8             rOh8   
     9             rOh9     
    10              

_.. then my output from my codes ( the second two from my first post ) is

    rOh2
    rOh7
    rOh9

The problem is that my codes as written wont let me start at first cell in my range – The .Find is written such that it starts looking After the Top Left if I do not specify an After:= argument.

If I do specify an After:= argument, then that must be in the search range. So I cannot specify an After:= as just before my adjusted search range. Well actually I can :) , sort of:

My next code does the business. Simply always start After:= the last cell in the range. – As Foxfire And Burns And Burns reminded us, the .Find ( and .FindNext ) keep going – when they get to the end of the search range they start again. By specifying After:= the last cell, then the search always starts at the first cell in the search range. In my codes that search range is updated to the range starting just after the last found cell.

It might be worth noting that the conventional code ( the very first one that I gave ) , whilst working well , it will, for the test data range I gave in this post, give the following:

    rOh2
    rOh3
    rOh7
    rOh8
    rOh9
    rOh1

You see that it has found the “first match” last. I expect that might cause some confusion if you were not aware of that.

So my modified simplified code below seems the best for me to use. It gives the output of

    rOh1
    rOh2
    rOh3
    rOh7
    rOh8
    rOh9

Here the code, and note the last cell should be empty

    Sub FindTheNext2()
    Dim rngFnd As Range
     Set rngFnd = Range("B1:B10").Find(What:="roh", after:=Range("B10"), LookAt:=xlPart)
        If rngFnd Is Nothing Then Exit Sub
        Do While Not rngFnd Is Nothing
         Debug.Print rngFnd.Value ' Do anything you wanna do                                                                                         http://www.youtuberepeater.com/watch?v=8GoN-y9irn4&name=Eddie+and+the+Hot+Rods+Do+anything+you+wanna
         Set rngFnd = Range("B" & rngFnd.Row + 1 & ":B10").Find(What:="roh", after:=Range("B10"), LookAt:=xlPart)
        Loop
    End Sub

If the last cell might be used , then this code will take care of that :

    Sub FindTheNext3()
    Dim rngFnd As Range
     Set rngFnd = Range("B1:B10").Find(What:="roh", after:=Range("B10"), LookAt:=xlPart)
        Do While Not rngFnd Is Nothing
         Debug.Print rngFnd.Value ' Do anything you wanna do                                                                                         http://www.youtuberepeater.com/watch?v=8GoN-y9irn4&name=Eddie+and+the+Hot+Rods+Do+anything+you+wanna
            If rngFnd = Range("B10") Then Exit Sub
         Set rngFnd = Range("B" & rngFnd.Row + 1 & ":B10").Find(What:="roh", after:=Range("B10"), LookAt:=xlPart)
        Loop
    End Sub

If there is any more deeply technical reason why the Findnext could be advantageous then I doubt anyone can remember.

Bottom line from me I think is don’t bother with it, I can’t see it as having much worth

Question: …… VBA Range.FindNext v Range.Find - what have I missed ?

Answer: …… Nothing , FindNext is a confusing waste of time. Just use .Find and make sure you know exactly how it works.

Answer 2

Ok, I see your point now. From my point of view:

FIND: This method will allow you to START a search, with the parameters you define. Every time you invoke Find, you are creating a NEW search process.

FINDNEXT: This method will CONTINUE a search started with a previous Find method, saving time of typing again parameters, and updating the range to search (actually, in your second and third codes you update the range where you are searching using Range("B" & rngFnd.Row + 1 & ":B10"). With FindNext, the search range is always the same (in your example, it's B1:B10), but VBA remembers the last position and resumes search from this last position.

So what is the point? Well, both methods will create an infinity loop unless you set a breakpoint. In your first code, the breakpoint is in the line Loop While Not rngFnd = FirstrngFnd. VBA remembers the first range where it searched and when loops again into this range, then it breaks the loops and resume codes. Your second and third code are infinite loops. They will never stop executing. Try it.

If you set up a similar breakpoint in your second and third codes, they won't work, because you are updating all the time the range to search, so there is no way it will search in the first range found with criteria, and it will be an infinite loop (Actually, the infinite loop will search in B10:b10 forever).

To set an effective breakpoint, you need both methods. Why? Because as I said at the beggining of my post, Find will start a new search every time you invoke it, so it won't move and you will create an infinite loop again. To make sure your search goes to next range that meets criteria defined, you need to use FindNext.

I hope this answer can give you a clue of how both methods work together. Anyways, you can read more info about this here:

Range.Find Method (Excel)

Range.FindNext