Results 1 to 5 of 5

Runtime Error 424 - Object Required

Threaded View

  1. #1
    Registered User
    Join Date
    05-21-2009
    Location
    Ok, USA
    MS-Off Ver
    Excel 2007
    Posts
    22

    Runtime Error 424 - Object Required

    Hey guys & gals.. I am getting a runtime error 424: Object required on the line "For Each ws In memberLists.Worksheet"

    The entire code is below. How can I fix this?

    Dim memberLists As Workbook
    Dim chapterColumn As String
    
    Sub ParseInputToWorksheets()
    
        Set memberLists = ActiveWorkbook
        chapterColumn = "E"
        
        Dim newMembers As Workbook
        Dim newMembersFilePath As String
        newMembersFilePath = "C:\...\NewMembers.xls"
        Set newMembers = GetObject(newMembersFilePath)
        
        Dim r As Range
        For Each r In newMembers.Worksheets(1).Rows
            Dim chapterName As String
            chapterName = r.Cells(1, chapterColumn)
            Call CopyToChapter(chapterName, r)
        Next
        
    End Sub
    
    Private Sub CopyToChapter(chapterName As String, r As Range)
        Dim ws As Worksheet
        For Each ws In memberLists.Worksheets
            If ws.Name = chapterName Then
                Dim iRow As Long
                    iRow = FirstEmptyRowIndex(ws)
                    r.Copy (ws.Rows(iRow))
                Exit Sub
            End If
        Next
    End Sub
    
    
    
    Private Function FirstEmptyRowIndex(ws As Worksheet) As Long
        Dim firstCell As Range
        Set firstCell = ws.Range("A1")
        Dim iRow As Long
        iRow = 0
        Do
            iRow = iRow + 1
            If firstCell.Offset(iRow, 0).Value = "" Then Exit Do
        Loop
        FirstEmptyRowIndex = iRow + 1
    End Function
    Last edited by teaker; 06-09-2009 at 04:02 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1