0

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

Community
  • 1
  • 1
Alan Elston
  • 89
  • 1
  • 4
  • 11
  • Your first code gets the values you want, I runned and it finds the values and prints them in Debugger Window, s what problem are you having exactly? – Foxfire And Burns And Burns Mar 04 '18 at 15:16
  • @ Foxfire And Burns And Burns Thxs for reply. All codes work. 2nd code is mine. 1st code is what is usually given or suggested and uses FindNext . I am trying to understand why the FindNext would be used. I am not a programmer. I am wondering if my ( 2nd ) code is stupid . I am trying to understand why FindNext would be used. I read all documentation lots of Blogs from internet. I can’t see reason to use it. What am I missing? FindNext seems redundant/ unnecessary. Why not use Find as I have in my (second) code?. No documentation answers. So I thought I would ask here that’s all. :) – Alan Elston Mar 04 '18 at 15:45
  • Edit march 9, 2018: Just to clarify. Ist code is that typically given on the Net. Code 2 is what I have been using. It uses Find and Find, ( two oiccurances in the code). Code 3 is similar to code Code 2 but usind Find and FindNext. Actually code 2 and code 3 were not quite working as I wanted . My Code Sub FindTheNext2() ( Answer 8th March German time - my second post - an Answer - My first Answer ) is the one I am finally happy with. :) – Alan Elston Mar 09 '18 at 06:45

2 Answers2

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

Community
  • 1
  • 1
Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38
  • @ Foxfire And Burns And Burns Thanks again for the reply. I think it confirms what I was understanding about the things, but the documentation is sparse so it is good to get it said by someone that knows. (I have those links in my post). Codes 2 & 3 have a break point (Loop While Not rngFnd Is Nothing) All codes work. None loop infinitely (UNLESS in codes 2 & 3 for the case that something is found in the last cell, then they loop infinitely. – But in my actual large files it is no problem to make sure the last cell is empty). Thanks again. Alan – Alan Elston Mar 04 '18 at 16:59
  • Just to clarify my reasoning behind Codes 2 & 3, and how they appear to work OK (without going into an infinite loop)- Something “may be found searching down”. (If never, then before the Loop I have Exit Sub). When something is found, the next search range starts from just after where the thing was found. Eventually nothing is found, so the break point ( Loop While Not rngFnd Is Nothing ) kicks in. I just must ensure that the last cell is empty so that if something is found in the last but one cell, the next search range (the last cell) gives rngFnd Is Nothing – Alan Elston Mar 04 '18 at 17:00
  • EDIT: In fact I dont even need _If rngFnd Is Nothing Then Exit Sub_ as my _Do While Not rngFnd Is Nothing_ will prevent anything being done for nothing found – Alan Elston Mar 04 '18 at 19:18
  • Just to clarify again. My codes ( the second 2 codes in my first post ) loop infinitely only in the case that something is found in the last cell ( B10 ). In such a case the next range to search is B11:B10. Then the thing in B10 would be found again, and the next range to search would be B11:B10 .. and so on… I try to clarify/ answer now below.. – Alan Elston Mar 08 '18 at 19:53
0

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.

Alan Elston
  • 89
  • 1
  • 4
  • 11