CODEWITHSUNDEEP
vbaexcelobject
Will
Will

Reputation: 33

"Object not set" error

I have a macro in Excel VBA that is giving me an error. I run it the first time right after I start up Excel and it runs perfectly but when I try to run it any time after that it errors out on the line nameRow = nameRange.Row giving the error Object not set and I am really confused as to why.

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

Could it be because of the way I am referencing the ActiveCell?

Upvotes: 2

Views: 54

Answers (2)

Rory
Rory

Reputation: 34075

You have a subsequent Find operation in the code that specifies only to look at the whole cell. This setting will persist so you probably just need to specify not to look at the whole cell in the first Find call:

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

Upvotes: 2

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19857

Does "user -name" exist the second time you run it?

 Set nameRange = membership.Range("A:A").Find("user -name")
    nameRow = nameRange.Row
    fullNameString = membership.Cells(nameRow, "A").Value

Check that nameRange returns a reference, and (I think), FIND remembers the last time it was used, so may be looking at cells below the previous FIND (see code below):

Dim nameRange As Range

Set nameRange = membership.Range("A:A").Find( _
    What:="user -name", _
    After:=membership.Range("A1"), _
    SearchDirection:=xlNext)

If Not nameRange Is Nothing Then
    'Do stuff if namerange found.
End If

Wouldn't this line return the same value as nameRange?

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

You're telling it to return the value in column A on the nameRow row, while nameRange is returning a reference to that same cell so can just pull back the Value of that?

Upvotes: 0

Related Questions