Results 1 to 2 of 2
  1. #1
    Monterey_Manzer is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    164

    Loop skips a large number of records unless stepped through

    Hi Everyone,



    Sorry for the poor grammar in the title. Okay here's the issue I'm running into. I have some code I'm writing to compare two different payment spreadsheets. At this point I'm just trying to collect the number of different payment types associated with each person. My problem is that, when I run the code normally, it skips over roughly 100 records before collecting any information. When I insert a breakpoint within my loop and step through each time everything works just fine. I can't figure out what I need to add or clean up in my loop to get the performance I do when I step through it. The code for my loop is shown below.

    Code:
        Dim lNameCol As Excel.Range, C As Excel.Range, sLastRow As Integer, i As Integer, numPay As Integer
        
        sLastRow = xlWs1.Range("A1").End(xlDown).Row
        
        
        i = 2
        'Set C = xlWs1.Range("E6:K6")
        'numPay = xl.WorksheetFunction.CountIf(C, ">" & 0)
        Do Until i > sLastRow
            Set C = xlWs1.Range("E" & i & ":" & "K" & i)
            numPay = xl.WorksheetFunction.CountIf(C, ">" & 0)
            If numPay > 0 Then
                Debug.Print xlWs1.Range("A" & i) & ", " & xlWs1.Range("B" & i)
            End If
            i = i + 1
        Loop
    Those two lines are commented out were my initial test of the logical comparison. I usually set the breakpoint at the incremental increase for variable i. If anyone can see some problem with the code here I would love some hints at what I can do to improve performance.

    Thanks!
    Ryan

  2. #2
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    I don't see any obvious problems with your code. I'm not sure how you know nothing is happening for your first 100 lines - my immediate window rolls off long before that many lines of displays. Perhaps if you printed every 10th message, rather than every message, to make sure that they aren't just rolling off?

    I can help with your performance issue. I found out a few months back that it's much more efficient to read/write chunks of data from excel to access. You can read a whole column into an array and process from memory much faster than you can read each cell - otherwise you're I-O bound, with all the time that Access and Excel and VBA are taking to pass information to each other.

    So, especially since you are just looking for numeric data > 0, you can read big chunks of Excel data into an Array and use VBA to test for data, rather than using the CountIf function.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 04-08-2013, 05:19 PM
  2. Replies: 30
    Last Post: 08-30-2012, 05:14 PM
  3. Query speed over a large number of records
    By GrantRawlinson in forum Queries
    Replies: 2
    Last Post: 06-15-2012, 11:03 AM
  4. Loop through records
    By sam10 in forum Programming
    Replies: 12
    Last Post: 07-07-2011, 02:30 PM
  5. Linking large number of forms and tables
    By jlcaviglia-harris in forum Forms
    Replies: 2
    Last Post: 04-17-2009, 09:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums