在递归函数中使用.Find [英] Using .Find in a Recursive Function

问题描述

我试图使用递归函数中的 .Find 函数在工作表中查找行号.我设置了一个名为 Found = .Find .... 的对象，它的工作原理非常好……有点.当我的递归深度为1级时，将其设置，然后当我的递归深度为2级时，将其再次设置.然后，我的代码找到路径的末尾并开始备份，直到它回到1级深为止，但是尚未重新声明我的Found对象并保留其2级值.我的其他变量(ThisRow等)保留了它们所在级别的值，这就是我想对Found对象执行的操作.有没有一种方法可以在本地声明Found，以便其值不会扩展到下一个函数，并且不能在更深层次上被覆盖?您可以在下面找到我的代码以供参考.

I am trying to find the row number in a sheet using the .Find function in a recursive function. I set an object called Found = .Find.... and it works great... for a little bit. I set it when I'm 1 level of recursion deep, then set it again when I'm 2 levels deep. Then, my code finds the end of the path and starts backing up until it gets back to 1 level deep, but not my Found object has been re-declared and kept its values from the 2nd level. My other variables (ThisRow etc...) keep the value of the level that they are in, and that's what I would like to do with the object Found. Is there a way that I can declare Found locally so that it's value doesn't extend to the next function, and can't be overwritten in a deeper level? You can find my code below for reference.

这是我当前的代码-删去了不相关的部分:

Here is my current code - irrelevant parts cut out:

Public Function FindChildren()

ThisRow = AnswerRow 'Also declared before function call

    BeenHereCell = Cells(ThisRow, "O").Address
    If Range(BeenHereCell).Value = "Yes" Then
        Exit Function 'That means we've already been there
    End If
    Range(BeenHereCell).Value = "Yes"

    With Worksheets("MasterScore").Range("j1:j50000")
        Set Found = .Find(NextQuestionID, LookIn:=xlValues)
        If Not Found Is Nothing Then
            firstAddress = Found.Address
            NextCell = Found.Address
            Do

                AnswerRow = Range(NextCell).Row
                FindChildren 'This is where it's recursive.

                Set Found = .FindNext(Found)
                NextCell = Found.Address

           Loop While Not Found Is Nothing And Found.Address <> firstAddress
        End If
    End With
End Function

现在我已经通过激活单元格解决了它，但是这使我的代码慢了很多.目前，我正在使用:

Now I have gotten around it by activating cells, but it makes my code a lot slower. Currently I am using this:

Set Found = Worksheets("MasterScore").Range("j1:j50000").Find(NextQuestionID, LookIn:=xlValues)
If Not Found Is Nothing Then
    Count = 1
    Do
        Columns("J:J").Select
        FirstFoundRow = Selection.Find(What:=NextQuestionID, After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Row
        For i = 1 To Count
            Selection.FindNext(After:=ActiveCell).Activate
        Next i
        AnswerRow = ActiveCell.Row
        If AnswerRow = FirstFoundRow And Count <> 1 Then Exit Do

        FindChildren
        Count = Count + 1
    Loop
End If

这样，我不必再次设置对象的值，而是需要遍历对象.FindNext多次，并且每次它运行该行时也会激活该行.我真的只想要类似的东西.

This way, I don't have to set the value of the object again, but I have to iterate through it.FindNext quite a few times and each time it runs that line its also activating the row. I really just want something like.

AnswerRow = .Find(nth instance of NextQuestionID).Row 

(我大约有5万行，而且计数经常会达到20行，所以确实需要一段时间).

(I have about 50k rows and the count goes to about 20 pretty often so it really takes a while).

任何想法，我将不胜感激！目前，我的代码正在运行，但是一天中要花很多时间才能完成，所以我需要在某个时候再次运行它！

I'd appreciate any ideas! Currently my code is working, but it's going to take a good part of the day to complete, and I'll need to run this again at some point!

推荐答案

我最终找到了一种加快速度的方法.我认为这可以帮助某人，所以我将分享我所发现的东西.这不是最好的解决方案(我宁愿只在本地声明该对象，这样我的其他函数就不会更改其值)，但是至少在此情况下，我不会循环遍历20个左右，而不会发现Do Loop的每个迭代.

I ended up finding a way to speed it up a little bit. I think this could help someone so I will share what I've found. It's not the best solution (I would have preferred to just declare the object locally so my other functions wouldn't change it's value), but at least with this I'm not looping through 20 or so finds every iteration of the Do Loop.

Set Found = Worksheets("MasterScore").Range("j1:j50000").Find(NextQuestionID, LookIn:=xlValues)
If Not Found Is Nothing Then
    NextAnswerRange = "j" & 1 & ":" & "j50000" 'The first search will be from the beginning

    Do
        Set Found = Worksheets("MasterScore").Range(NextAnswerRange).Find(NextQuestionID, LookIn:=xlValues)
        NextCell = Found.Address
        AnswerRow = Range(NextCell).Row

        NextAnswerRange = "j" & AnswerRow & ":" & "j50000"
        If LastAnswerRange = NextAnswerRange Then Exit Function 'This would mean we've reached the end.
        LastAnswerRange = NextAnswerRange

        FindChildren
    Loop
End If

结束功能

因此，我们知道我们已经用先前的范围覆盖了我们的基础，因为它始终可以找到下一个范围.我们每次都更改搜索范围，它将找到下一个值.

So we know we've already covered our bases with previous ranges since it always finds the immediate next. We just change the range of the search each time and it will find the next value.

关于此解决方案的一件奇怪的事情是，如果您正在寻找范围70-> 50,000之间的值，并且在第70行找到了答案，它实际上会找到下一行(它跳过了第一行).但是，如果70岁以上没有答案的行，那么它将实际上从70行取值.这意味着我做不到

A weird thing about this solution is that if you are looking for a value among range 70 -> 50,000 and you have the answer your looking for on row 70, it will actually find the next row (it skips that first one). But, if there aren't any rows past 70 that have the answer, it will actually take the value from row 70. That meant that I couldn't do

NextAnswerRange = "j" & AnswerRow + 1 & ":" & "j50000"

因为它将丢失一些值.如果没有在文档末尾加上+ 1，那我将一遍又一遍地搜索相同的最后一个值(它永远不会返回到Found Is Nothing)，所以我不得不检查一下LastAnswerRange =NextAnswerRange.

because it would miss some values. Doing it without the + 1 meant at the end of the document I would end up searching the same last value over and over (it would never go back to Found Is Nothing) so I had to put in the check to see if the LastAnswerRange = NextAnswerRange.

我希望这对某人有帮助.我认为这不是最优雅的解决方案，但是它比我拥有的解决方案快得多.

I hope this helps someone. I don't think it's the most elegant solution, but it's a lot faster than what I had.

这篇关于在递归函数中使用.Find的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！