vba - "Object not set"错误

Question

我在 Excel VBA 中有一个宏，它给了我一个错误。我在启动 Excel 后第一次运行它，它运行良好，但是当我尝试在之后的任何时间运行它时，它会在 nameRow = nameRange.Row 行出错给出错误 Object not set我真的很困惑为什么。

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")

    membership.Activate
    Set nameRange = membership.Range("A:A").Find("user -name")
    nameRow = nameRange.Row
    fullNameString = membership.Cells(nameRow, "A").Value
    nameIndex = InStr(fullNameString, "user -name")
    barIndex = InStr(fullNameString, "|")
    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)




End Sub

可能是因为我引用 ActiveCell 的方式吗？ ?

Answer 1

您有一个后续 Find指定仅查看整个单元格的代码中的操作。此设置将持续存在，因此您可能只需要在第一个 Find 中指定不查看整个单元格即可。称呼:

Set nameRange = membership.Range("A:A").Find(What:="user -name", Lookat:=xlpart)