在vba Excel中大量查找请求(在查找中查找) [英] Multple find requests in vba Excel (Find within a Find)

问题描述

我正在尝试执行一种嵌套的查找请求，用例是我需要在一个工作表上查找一个组，如果找到，则从找到的行中的单独列中获取用户ID值，然后搜索该ID在另一张纸上.然后，它应该执行一系列操作，然后在第一张工作表中找到该组的下一个出现.

I am trying to perform a type of nested find request, the use case is that I need to look for a group on one worksheet, if found take the user ID value from a seperate column in the found row and then search for that ID in another sheet. It should then perform a bunch of actions and then find the next occurance of group in the first sheet.

我的代码是

LookupGroup = Split("GroupName1,GroupName2", ",")
For I = 0 To UBound(LookupGroup)
    With Worksheets("RawData").Range("C:C")
        Set C = .Find(LookupGroup(I), LookIn:=xlValues)
        If Not C Is Nothing Then
            FirstAddress = C.Address
            Do
                LookupId = Sheets("RawData").Cells(C.Row, 7).Value
                IdExist = False
                'Check to ensure ID does not exists on Team Members Tab
                Set IdRange = Sheets("Team Members").Range("A:A").Find(LookupId, LookIn:=xlValues)
                If IdRange Is Nothing Then
                    IdExist = True
                End If
                If Not IdExist Then
                    Highlight = True 'trigger to Set row to bold red font
                    If RecordsFound > 0 Then
                        TotalRecords = TotalRecords + RecordsFound
                    End If
                End If
                Set C = .FindNext(C)
            Loop While Not C Is Nothing And C.Address <> FirstAddress
        End If
    End With
Next I

这在第一次使用时效果很好，但是到达Set C = .FindNext(C)时，该命令返回"Nothing"，而不是下一次出现.

This works fine the first time through, however upon reaching the Set C = .FindNext(C) the command returns 'Nothing' rather than the next occurence.

如果我注释掉第二个发现

If I comment out the second find

Set IdRange = Sheets("Team Members").Range("A:A").Find(LookupId, LookIn:=xlValues)

然后第一次搜索可以正常工作并找到所有实例

Then the first search works fine and finds all instances

我在做什么错了?

推荐答案

更容易采用Find()逻辑并将其放在单独的函数中...

Easier to take the Find() logic and put it in a separate function...

Sub Tester()
Dim LookupGroup, rngGrp As Range, rngMember As Range, I
Dim g As Range, m As Range

    LookupGroup = Split("GroupName1,GroupName2", ",")

    For I = 0 To UBound(LookupGroup)

        Set rngGrp = FindAll(Worksheets("RawData").Range("C:C"), LookupGroup(I))

        If Not rngGrp Is Nothing Then
            For Each g In rngGrp.Cells

                Set rngMember = FindAll(Sheets("Team Members").Range("A:A"), _
                                        g.EntireRow.Cells(7))

                If Not rngMember Is Nothing Then
                    For Each m In rngMember.Cells
                        'do something with m
                    Next m
                Else
                    'flag not found...
                End If
            Next g
        End If
    Next I

End Sub

'find all matching cells in a given range
Function FindAll(rngLookIn As Range, LookFor) As Range

    Dim rv As Range, c As Range, FirstAddress As String
    With rngLookIn
        Set c = .Find(LookFor, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            FirstAddress = c.Address
            Set rv = c
            Do
                Set c = .FindNext(c)
                If Not c Is Nothing Then Set rv = Application.Union(rv, c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
        End If
    End With
    Set FindAll = rv
End Function

这篇关于在vba Excel中大量查找请求(在查找中查找)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！