Results 1 to 4 of 4

Endless Loop?

Threaded View

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

    Endless Loop?

    Hi again... Having another problem with the same macro that has been giving me trouble for a week now. Now, the macro will run, but after it finishes, excel stops responding. Is this because the macro continues to run indefinitely? Any ideas?

    Sub ParseInputToWorksheets()
    
        Dim memberLists As Workbook
        Dim chapterColumn As String
        Set memberLists = ActiveWorkbook
        chapterColumn = "B"
        
        Dim newMembers As Workbook
        Dim newMembersFilePath As String
        newMembersFilePath = "C:\Creeds\Database\SAE\NewList.xlsx"
        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 memberLists As Workbook
        Dim ws As Worksheet
        Set memberLists = ActiveWorkbook
        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
    Attached Files Attached Files
    Last edited by teaker; 06-16-2009 at 10:01 AM.

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