vba - VBA 中的 FindNext 无法继续

Question

我有一个 vba 脚本，用于处理一组数据，如下所示。它会选择用户名，在本例中为“管理员”，然后转到另一个电子表格并搜索该用户名。一旦找到它，它就会返回到另一个电子表格并向下移动一个单元格，并在另一个电子表格的该单元格内搜索文本。在与列(包含用户名)和行(包含另一组数据)相交的单元格中，它标记为“X”。然后，它循环遍历这组数据，对该用户名下的每条数据执行此操作。然后它转移到下一组具有相同格式的数据，并再次执行相同的过程。或者至少应该是这样。有关此脚本的所有内容都有效，除了在到达第一组数据的底部(这是一个空单元格)之后，它没有正确设置范围以查找下一个匹配项。由于某种原因，FindNext 调用找到“Users,Builtin”，这是第一组中的最后一条数据。我真的很困惑为什么会发生这种情况。我有一种感觉，这可能与我使用 ActiveCell 的方式有关，但我真的不确定。任何有关此问题的帮助将不胜感激!

脚本:

Sub AssignGroups()

    Dim membership As Worksheet
    Dim wb As Workbook
    Dim groups As Worksheet
    Dim nameRow As Long
    Dim fullNameString As String
    Dim nameRange As Range
    Dim groupRange As Range
    Dim nameRange2 As Range
    Dim nameIndex As Long
    Dim userNameString As String
    Dim barIndex As Long



    Set wb = ActiveWorkbook
    Set membership = Sheets("User Group Membership")
    Set groups = Sheets("User Assigned to Groups")
    Set nameRange = membership.Range("A:A").Find("user -name", LookAt:=xlPart)


    If Not nameRange Is Nothing Then

        firstAddress = nameRange.Address

    Do

    membership.Activate
    nameRow = nameRange.Row
    MsgBox (nameRow)
    fullNameString = membership.Cells(nameRow, "A").Value
    'MsgBox (fullNameString)
    nameIndex = InStr(fullNameString, "user -name")
    barIndex = InStr(fullNameString, "|")
    'MsgBox (nameIndex)
    'MsgBox (barIndex)
    userNameString = Mid(fullNameString, nameIndex + 12, ((barIndex - 4) - (nameIndex + 12)))

    groups.Activate
    Set nameRange2 = groups.Range("A:CH").Find(userNameString)
    nameColumn = nameRange2.Column



    membership.Activate
    membership.Cells(nameRow, "A").Activate

    Do
        ActiveCell.Offset(1).Activate

        If Not IsEmpty(ActiveCell.Value) Then

            cellValue = ActiveCell.Value
            groups.Activate
            Set groupRange = groups.Range("A:CH").Find(cellValue, , , LookAt:=xlWhole)
            groupRow = groupRange.Row
            groups.Cells(groupRow, nameColumn).Activate
            ActiveCell.Value = "X"
            membership.Activate

         End If



        Loop Until IsEmpty(ActiveCell.Value)

    Set nameRange = membership.Range("A:A").FindNext(nameRange)
    MsgBox (nameRange.Address)

    Loop While Not nameRange Is Nothing Or nameRange.Address <> firstAddress
    End If


End Sub

数据:

C:\Documents and Settings\Administrator.CHESCONETAD>dsquery user -name "Administrator"   | dsget user -memberof -expand 
Administrators,Builtin
Domain Admins,Users
Domain Users,Users
EMLibrary Users,Users
Enterprise Admins,Users
Exchange Organization Administrators,OU=Microsoft Exchange Security Groups
Exchange Public Folder Administrators,OU=Microsoft Exchange Security Groups
Exchange Recipient Administrators,OU=Microsoft Exchange Security Groups
Exchange View-Only Administrators,OU=Microsoft Exchange Security Groups
Group Policy Creator Owners,Users
Schema Admins,Users
SophosAdministrator,Users
Users,Builtin

Answer 1

您对“ActiveCell”导致问题的怀疑是正确的。最好避免使用ActiveCell和Activate和Select`语句，除非您绝对需要它们。最好直接使用对象。

我认为如果您将 Until 语句放在第二个 Do Loop 中的 Do 旁边，而不是放在 Loop 旁边 它也会工作得更好。

最后，由于您在代码中使用多个查找，FindNext 命令使用您在 Find 对话框中输入的最后一个内容，因此您需要调整该语句以及做你想做的事。

请参阅下面我重构的代码，如果它不能解决问题，请告诉我。请注意我如何限定所有变量，更改第二个循环中的 Until 位置，并调整 .FindNext 行。

Sub AssignGroups()

    Dim membership As Worksheet
    Dim wb As Workbook
    Dim groups As Worksheet
    Dim nameRow As Long
    Dim fullNameString As String
    Dim nameRange As Range
    Dim groupRange As Range
    Dim nameRange2 As Range
    Dim nameIndex As Long
    Dim userNameString As String
    Dim barIndex As Long

    Set wb = ActiveWorkbook
    Set membership = Sheets("User Group Membership")
    Set groups = Sheets("User Assigned to Groups")


    With membership

        Set nameRange = .Range("A:A").Find("user -name", LookAt:=xlPart)


        If Not nameRange Is Nothing Then

            firstAddress = nameRange.Address

            Do

                nameRow = nameRange.Row

                'MsgBox (nameRow)

                fullNameString = .Cells(nameRow, "A").Value

                'MsgBox (fullNameString)

                nameIndex = InStr(fullNameString, "user -name")
                barIndex = InStr(fullNameString, "|")

                'MsgBox (nameIndex)
                'MsgBox (barIndex)

                userNameString = Mid(fullNameString, nameIndex + 12, ((barIndex - 4) - (nameIndex + 12)))

            With groups

                Set nameRange2 = .Range("A:CH").Find(userNameString)
                nameColumn = nameRange2.Column

            End With


            Do Until IsEmpty(.Cells(nameRow, "A").Offset(1))

                cellValue = .Cells(nameRow, "A").Offset(1)

                With groups

                    Set groupRange = .Range("A:CH").Find(cellValue, , , LookAt:=xlWhole)

                    groupRow = groupRange.Row
                    .Cells(groupRow, nameColumn).Value = "X"

                End With

                nameRow = nameRow + 1

            Loop

            Set nameRange = .Range("A:A").Find("user -name", After:=nameRange, LookAt:=xlPart)

        MsgBox (nameRange.Address)

        Loop While Not nameRange Is Nothing Or nameRange.Address <> firstAddress

    End If

End Sub