在递归函数中使用.Find

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.

我试图在递归函数中使用.Find函数在工作表中找到行号。我设置了一个名为Found = .Find ....的对象,它的效果很好......一点点。我在深度为1级递归时设置它,然后在我2级深度时再次设置它。然后,我的代码找到路径的结束并开始备份,直到它返回到1级深,但不是我的Found对象已被重新声明并保持其值从第2级。我的其他变量(ThisRow等...)保持它们所处的级别的值,这就是我想要对象Found所做的事情。有没有办法可以在本地声明Found,以便它的值不会扩展到下一个函数,并且不能在更深层次上覆盖?您可以在下面找到我的代码以供参考。

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

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.

这样,我不必再次设置对象的值,但我必须遍历it.FindNext很多次,并且每次运行该行时它也会激活该行。我真的只想要类似的东西。

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

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

(我有大约5万行,计数经常大约20,所以它真的需要一段时间)。

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!

我很感激任何想法!目前我的代码正在运行,但它需要花费很多时间来完成,我需要在某个时候再次运行它!

1 个解决方案

#1

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.

我最终找到了一种方法来加快它的速度。我想这可以帮助别人,所以我会分享我发现的东西。这不是最好的解决方案(我本来希望只在本地声明对象,这样我的其他函数就不会改变它的值),但至少在这种情况下,我不会在20左右循环查找Do循环的每次迭代。

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

End Function

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.

所以我们知道我们已经覆盖了先前范围的基础,因为它总能找到下一个范围。我们每次只更改搜索范围,它会找到下一个值。

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

关于这个解决方案的一个奇怪的事情是,如果你正在寻找范围70 - > 50,000之间的值,并且你在第70行找到你想要的答案,它实际上会找到下一行(它跳过第一行)。但是,如果没有任何超过70的行有答案,它实际上将从第70行获取值。这意味着我不能这样做

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

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.

因为它会错过一些价值观。在没有+ 1的情况下执行此操作意味着在文档的最后我会一遍又一遍地搜索相同的最后一个值(它永远不会回到Found Is Nothing)所以我必须检查以查看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.

我希望这可以帮助别人。我不认为这是最优雅的解决方案,但它比我的速度快得多。