+ Reply to Thread
Results 1 to 4 of 4

Endless Loop?

Hybrid 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.

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,645

    Re: Endless Loop?

    Hi there,

    I haven't checked all of your code, but this suggestion might be a good place to start!

    Regarding
    Is this because the macro continues to run indefinitely?
    the macro isn't running "indefinitely", but because of
    For Each r In newMembers.Worksheets(1).Rows
    it's happily processing all 65536 rows in the worksheet!
    Try the following code which will limit the macro's "attention" to the rows between Row 2 and the row which contains the last data record:
    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 memberRows As Long
        memberRows = newMembers.Worksheets(1).UsedRange.Rows.Count
    
        Dim r As Range
            For Each r In newMembers.Worksheets(1).Range(newMembers.Worksheets(1).Rows(2), _
                                                         newMembers.Worksheets(1).Rows(memberRows))
    
            Dim chapterName As String
            chapterName = r.Cells(1, chapterColumn)
            Call CopyToChapter(chapterName, r)
        Next
    
    End Sub
    I hope this helps - please let me know how you get on or if you need anything further.

    Regards,

    Greg M

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

    Re: Endless Loop?

    Ah, worked like a charm!

    Thanks!!

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,645

    Re: Endless Loop?

    Hi again,

    Many thanks for your feedback - I'm glad I was able to help.

    Regards,

    Greg M

+ Reply to Thread

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